Feroz DBA'S Blog

  We do detach and attach a database to improve the Performance, to move the physical drive location of datafiles on the same server or to different server and due to space constraints. Steps to Detach and Attach a database: Step-1: Change the Restrict Access to Single mode Right click on database (devdb) -> Properties -> Options -> modify the Restrict Access to Single mode -> Ok Step-3: Right click on database (devdb) -> Tasks -> Detach -> select "Drop connections" and "Update Statistics" -> Ok Step-4: Move the datafiles and logfiles from old location to the new location from OS level  Old location: C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA New location: D:\Datafiles\devdb Step-5: Right click on Databases -> Attach -> select the datafile from new location. Step-6: Validate the database attached and the file locations. Manual Method using Script: Step-1: Locate the database file location sp_helpdb devdb Step-2...

 Steps to install SQL Server 2012 on Windows

What is SQL Server TempDB and it's Purpose. SQL Server TempDB is a system database that is automatically created whenever you install the SQL Server on a machine and also it creates each time when you start the SQL Service. The Purpose of the SQL Server TempDB is, it used by the SQL Server and the database users for sorting operations and it's used to create Global or Local temporary tables, and Temporary stored procedures. Additionally, the TempDB can also be used to offload sorting from main database to TempDB while index creation using SORT_IN_TEMPDB to improve the performance. Important points to be remembered for TempDB: you can't drop it like normal database You can't back up and restore  You can't change it's recovery model  You can't create multiple file groups  You can't detach it like normal database Best practices about TempDB configuration: Should always keep both the datafile and logfile on a separate disk  Always place the files on the fast...