[Memo★Database: database-related memos

Truncate database logs

Alter database edmdb
Set recovery simple; -- set the simple recovery mode
Go
DBCC shrinkfile (edmdb_log, 1); -- use this statement to obtain names marked in red: Select * From SYS. database_files
Go
Alter database database_name
Set recovery full; -- restore to original mode
Go

Use strings as in statement parameters during stored procedures

Problem description: In the stored procedure, use a string as the in statement parameter and directly pass the parameter either with a syntax error or with incorrect results.

Solution: it can be implemented in disguise using the charindex function.

Where charindex (ID, @ id_array)> 0 is equivalent to where ID in (@ id_array)

 

Unexpected database termination, displaying "suspicious" Solutions

ALTER DATABASE DBName  SET EMERGENCY ALTER DATABASE DBName  SET SINGLE_USERDBCC CheckDB (DBName  , REPAIR_ALLOW_DATA_LOSS) ALTER DATABASE DBName  SET MULTI_USER 

 

Database restoration error: "unable to obtain exclusive access to the database because the database is in use" SOLUTION

Declare @ dbname varchar (20) set @ dbname = 'cshow '-- change the name of the database that occupies the connection to declare @ SQL nvarchar (500) declare @ spid int -- The spid value is a unique Integer Set @ SQL = 'descare getspid cursor forselect spid from sysprocesses where dbid = db_id (' ''' + @ dbname + ''') 'exec (@ SQL) Open getspidfetch next from getspid into @ spidwhile @ fetch_status <>-1 -- if the fetch statement does not fail to be executed or this row is not in the result set. Beginexec ('Kill '+ @ spid) -- terminate the normal connection fetch next from getspid into @ spidendclose getspiddeallocate getspid