SQL
SQL: attached a mdf without the log file.
by admin on Feb.19, 2010, under SQL
Missing ldf file
Solution
You can attach a database without the logfile by attaching, then clicking the logfile location, and click the remove button. After you click OK a new logfile will be created for your database
sQL: SQL 2000 truncate logs using DBCC
by admin on Jan.22, 2010, under SQL
DBCC statememnts act as Database Console commands. You can use the below listed queries, sprcifically the DBCC ones to shrink your transaction log.
– To GET the name of the log and data files
select name from dbo.sysfiles
– SEE the active transactions as well as the File ID
dbcc loginfo(‘db_Name’)
– Mark transactions inactive so that they can be truncated later
– Suppose file ID is 2
DBCC SHRINKFILE (2)
– Backup the log file and truncate the inactive entries
BACKUP LOG [db_name] WITH TRUNCATE_ONLY
– Run Shrinkfile again for maintainence.
DBCC SHRINKFILE (2)
You can create a stored procedure out of the last 3 statements and schedule it as a job on your server than can run overnight and ensure that the transaction log file size does not get out of hand.
SQL 2008 : Delete all tables in a db
by admin on Nov.27, 2009, under SQL
EXEC sp_MSforeachtable @command1 = “DELETE FROM ?”
EXEC sp_MSforeachtable @command1 = “TRUNCATE TABLE ?”
——
You won’t be able to run TRUNCATE against all tables if you have foreign keys references
Here is one way to circumvent that
– First disable referential integrity
EXEC sp_MSForEachTable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL’
GO
EXEC sp_MSForEachTable ‘
IF OBJECTPROPERTY(object_id(”?”), ”TableHasForeignRef”) = 1
DELETE FROM ?
else
TRUNCATE TABLE ?
‘
GO
– Now enable referential integrity again
EXEC sp_MSForEachTable ‘ALTER TABLE ? CHECK CONSTRAINT ALL’
GO
SQL: HRESULT 0×80070008
by admin on Aug.21, 2009, under SQL
Error:
Failed to open malformed assembly ‘mscorlib’ with HRESULT 0×80070008.
If you are sure your SQL query is correct trying having your webhost restart the SQL server engine. This error could mean the SQL process does not have enough memory to process your request..
other error:
There is insufficient system memory in resource pool to run this query.
SQL: The DTS Wizard for importing and exporting
by admin on Jul.26, 2009, under SQL
We found some article relating to the DTS Wizard which is a good tool to use for exporting and importing db structures.
so far we have not found the toolkit for SQL 2008 and assume the 2005 version works with 2008. If you have confirmed this please feel free to contact me and correct this information.
You can download the DTS Tool via the 2005 toolkit which can be downloaded from Microsoft.
Additional information
Microsoft Connect
