A union of curiosity and data science

Knowledgebase and brain dump of a database engineer


Shrink Transaction Logs

 
Running the following code truncates the transaction log. A full database backup should be run after this script to insure point of time recovery in any production environment. 

USE <DB>
GO
DBCC SHRINKFILE(<TLOGNAME>, 1)
BACKUP LOG <DB> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TLOGNAME>, 1)
GO

 
 
-- New way
use <DB>
ALTER DATABASE <DB> SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE(<DB_LOG>, 1)
ALTER DATABASE <DB> SET RECOVERY FULL WITH NO_WAIT

TSQL Query Default Database Path for SQL Server


 
declare @SmoDefaultFile nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @SmoDefaultFile OUTPUT

declare @SmoDefaultLog nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', @SmoDefaultLog OUTPUT

SELECT ISNULL(@SmoDefaultFile,N'') AS [DefaultFile], ISNULL(@SmoDefaultLog,N'') AS [DefaultLog]

 

 

SQL 2K5 Suspect Mode Database (EMERGENCY MODE)

 
SQL server can become marked suspect for many different reasons. 
File corruptions, hardware issues, OS issues, translog errors and on and on ... 
 
Find the cause and then get your database back..... 
 
Find the suspect database:
SELECT * FROM master.sys.databases WHERE state_desc='SUSPECT'
 

Now Fix the database:

USE master;
GO


---configure database set option: ONLINE:OFFLINE:EMERGENCY.
---Emergency: allows access to the database for data moving.(Read Only Mode)

ALTER
DATABASE sandbox SET EMERGENCY
GO

--- EMERGENCY MODE ALLOWS FOR DBCC CHECKDB, Allow data loss option requires single user mode.
ALTER
DATABASE sandbox SET SINGLE_USER
GO

/*
if this statement succeeds the database is brought back online.
you'll need to set it to multi-user mode.
if it fails, there's no way to recover. You'll need to restore from backup.
NO_INFOMSGS : Suppresses all informational messages (Severity 10) and the report of space used.*/

DBCC
CHECKDB (sandbox, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
GO

--- use my db.
USE
sandbox
GO

--- Checks the allocation and structural integrity of all the objects in the specified database.
DBCC
CHECKDB WITH NO_INFOMSGS;