SQL Hyenas

SQL Allow you to shrink data and log file.   You can shrink a file to a size that is less than the size specified when it was created. This resets the minimum file size to the new value. We will discussed here some ways to shrink database files. Before we move to shrink file using queries, first we need to get names of files which are attached to the database. Below is the query which is used to retrieve logical name of mdf and ldf files. Get Name of mdf and lfd file Syntax: USE Database_Name GO EXEC sp_helpfile GO Shrink database file using queries By using this query you will be find name of files. No we will move to use this file name into the query to shrink those files. Syntax: Use Database_Name DBCC SHRINKFILE (FileNameToShrink' , 1) SHRINKFILE requires two parameter. First is the name of file to shrink and other is size for the file in megabytes, expressed as an integer. If not specified, DBCC SHRINKFILE reduces the size t...

If you try to copy .mdf  and .lfd file to new location, sql server not allow you to do so, it will through the error. For that you need to follow certain steps. Below are the steps which will tell you how to achieve this task. STEP 1:  Alter Database to Single User. To copy a log of database you need to Detach the log file from database so for that you need to make database  Single User  First. Syntax: ALTER DATABASE  Database_Name SET SINGLE_USER WITH ROLLBACK IMMEDIATE; STEP 2:  kill other session of database If you switch database mode multiple to single you need to kill other session to use single mode database and do some operations. Below is the query which is returns current session of the database Syntax: select d.name, d.dbid, spid, login_time, nt_domain, nt_username, loginame from sysprocesses p inner join sysdatabases d on p.dbid = d.dbid where d.name = 'Database_Name' Above query will retur...