Buffer Management of SQL Server: SQL Server uses buffer pages for any changes or requests for the data on the database. These buffer pages are 8KB pages on RAM that minimizes disc Input/Output. Any 8KB page can be buffered in memory, and set of all pages currently buffered is called as buffer cache. The amount of memory available to SQL Servers decides how many pages will be cached in memory. The buffer cache is managed by Buffer Manager. Either reading from or Writing to any page on the disc happens at two levels. The data that should be either read/Written is first copied to the buffer cache and the read/write operation is carried out on the data that is currently on the Buffer Cache. The edited data is updated on the disc by the buffer manager only if the in-memory cache has not been referenced for some time. While writing pages back to the disc, asynchronous I/O is used whereby the I/O operation is done in a background thread so that other operations do not have to wait for the I/...
Posts
Hi People, check out this link. It discusses about searching the error log from the query editor which allows us to be more flexible in spotting out the exact error we need. http://www.mssqltips.com/tip.asp?tip=1476
The following query can be used to drop the locks on Database objects. It should be used carefully as the false killing of the processes would lead to disastrous issues which would come as chain of issues. Create Table #Tmp ( spid smallint, ecid smallint, status nchar(30), loginame nchar(128), hostname nchar(128), blk char(5), dbname nchar(128), cmd nchar(16) ) Create Table #TmpLocks ( spid smallint, dbid smallint, ObjId int, IndId smallint, Type nchar(4), Resource nchar(16), Mode nvarchar(8), Status nvarchar(28) ) Insert Into #Tmp Exec sp_who Insert Into #TmpLocks Exec sp_lock If(Select Count(*) From #Tmp T Join #TmpLocks TL On T.spid = TL.spid Where /*This is for tempdb*/ dbid = 2 And objid In (1, 2, 3)) > 0 Then you can kill the concerned spid with the command : Kill — The concerned spid Drop Table #Tmp Drop Table #TmpLocks
Its often to have issues on transaction log getting full with an error: The Transaction Log for the database ABC is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in the SYS.DATABASES System catalog view. If we execute the following query in the query editor, select log_reuse_wait_desc from sys.databases where name = 'ABC'; we get one of the follwing possibilities: Reuse of transaction log space is currently waiting on one of the following: 0 = Nothing Currently there are one or more reusable virtual log files. 1 = Checkpoint No checkpoint has occurred since the last log truncation, or the head of the log has not yet moved beyond a virtual log file (all recovery models). 2 = Log backup A log backup is required to move the head of the log forward (full or bulk-logged recovery models only). Note: Log Backup do not prevent truncation 3 = Active backup or restore data backup or a restore is in progress (all recovery...
Methods of Data Pull from Excel to Database Tables: There are several methods that can be used to pull the data from an excel sheet into a database table. Using SQL Server's Import and Export wizard that lets us choose the excel file from which the data to be pulled. Each sheet of the excel file can be distinguished from the sheet name during the mapping step in the wizard. A sheet, for example sheet 1 is referred as Sheet1$ and so is Sheet2$ and so on. Creating an SSIS package, that lets us choose the data source to be an excel file and the data destination that would be a database table through a OLE DB Connection properly set up. Another way would be using a linked server. Here, select the OLE DB provider to be Microsoft Jet 4.0 OLE DB Provider. Select the Product Name as Excel, Data source is the full path of the Excel file and the provider string is Excel 8.0. This configuration is as mentioned in the below picture. And later, click OK, which will complete the configur...
The following query is used to get the SQL Server Uptime. Implicitly defines the SQL Server Services uptime. USE [master] GO /****** Object: StoredProcedure [dbo].[GetSQLUptime]******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[GetSQLUptime] AS BEGIN Declare @theMinutes int Set @theMinutes = (SELECT TOP 1 DATEDIFF(mi,login_time, GETDATE()) AS TotalUpTimeInMinutes FROM sys.sysprocesses ORDER BY login_time) Select @@SERVERNAME AS ServerName, convert(varchar(15), @theMinutes / 1440 ) + ' Days, ' + REPLICATE('0', 2 - DATALENGTH(convert(varchar(2), (@theMinutes % 1440) / 60 ))) + convert(varchar(2), (@theMinutes % 1440) / 60 ) + ' Hours, ' + REPLICATE('0', 2 - DATALENGTH(convert(varchar(2), (@theMinutes % 60)))) + convert(varchar(2), (@theMinutes % 60)) + ' Minutes' AS SQLUptime_Days_Hours_Minutes END GO
When you back up a database, determine whether to overwrite or append to a backup file. The SQL Server default is to append (NOINIT) backups to a file. If you use the NOINIT option, SQL Server appends a backup to an existing backup file or backup set. If you use the INIT option, SQL Server overwrites any existing data on the backup media set, but it retains the header information. If the first file of the backup set on the device has an ANSI-standard label, SQL Server determines whether the previous backup set can be overwritten. Use the FORMAT option to overwrite the contents of a backup file and split up the backup set. --Backup device USE master EXEC sp_addumpdevice 'disk', 'AdventureWorksBack','D:\MyBackupDir\AdventureWorksBack.bak' --to see the media contents of the backup device RESTORE filelistonly from AdventureWorksBack RESTORE HEADERONLY from AdventureWorksBack --full backup --Full backup on backup device BACKUP DATABASE Adventu...
Here is a script to check the status of SQL Server Services and there status from the SSMS /*---------------------------------------*/ /* SQL Server Service Check Utility */ /*---------------------------------------*/ SET NOCOUNT ON /* ------------------------------------------ Inital Setup -----------------------------------------------------*/ CREATE TABLE #RegResult ( ResultValue NVARCHAR(4) ) CREATE TABLE #ServicesServiceStatus /*Create temp tables*/ ( RowID INT IDENTITY(1,1) ,ServerName NVARCHAR(128) ,ServiceName NVARCHAR(128) ,ServiceStatus varchar(128) ,StatusDateTime DATETIME DEFAULT (GETDATE()) ,PhysicalSrverName NVARCHAR(128) ) DECLARE @ChkInstanceName nvarchar(128) ...
SELECT o.name AS "Table Name", i.rowcnt AS "Row Count" FROM sysobjects o, sysindexes i WHERE i.id = o.id AND indid IN(0,1) AND xtype = 'u' AND o.name <> 'sysdiagrams' ORDER BY i.rowcnt DESC
For a DBA its a routine issue to keep watch on the tables and database usage. Hence, it should be possible for any DBA to track the changes made to either a database or a table and be able to take necessary action according to the situation. Consider, if there is a database which is never used in a server but still there in the server. The metadata of the idle database will be stored in the system databases as this is connected to the server and if the database is very big with many objects, its obvious that a huge data should be stored in the system databases related to that database. Hence to counter the problem we can check the server databases which are critical in respect to its usage and if we found some databases never being used, we can take them offline. We can check the database, when it was last used or updated by running a query as shown below. select distinct DB_NAME(database_id), object_name(object_id), last_user_seek,last_user_scan, last_u...
Credentials (Database Engine) A credential is a record that contains the authentication information (credentials) required to connect to a resource outside SQL Server. This information is used internally by SQL Server. Most credentials contain a Windows user name and password. The information stored in a credential enables a user who has connected to SQL Server by way of SQL Server Authentication to access resources outside the server instance. When the external resource is Windows, the user is authenticated as the Windows user specified in the credential. A single credential can be mapped to multiple SQL Server logins. However, a SQL Server login can be mapped to only one credential. System credentials are created automatically and are associated with specific endpoints. Names for system credentials start with two hash signs (##). To check the credentials available from an instance os SQL Server, execute select * from sys.credentials To create a credential In Object Explorer,...