What Is Filling Up The Transaction Log

SELECT db.[name] AS [Database Name] ,
 db.recovery_model_desc AS [Recovery Model] ,
 db.log_reuse_wait_desc AS [Log Reuse Wait Description] ,
 ls.cntr_value AS [Log Size (KB)] ,
 lu.cntr_value AS [Log Used (KB)] ,
 CAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT)
 AS DECIMAL(18,2)) * 100 AS [Log Used %] ,
 db.[compatibility_level] AS [DB Compatibility Level] ,
 db.page_verify_option_desc AS [Page Verify Option]
FROM sys.databases AS db
 INNER JOIN sys.dm_os_performance_counters AS lu
 ON db.name = lu.instance_name
 INNER JOIN sys.dm_os_performance_counters AS ls
 ON db.name = ls.instance_name
WHERE lu.counter_name LIKE 'Log File(s) Used Size (KB)%'
 AND ls.counter_name LIKE 'Log File(s) Size (KB)%' ;


Reference: http://www.sqlservercentral.com/scripts/DMV/71997/

Find what script is being run by specific SPID number

DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = 53
SELECT TEXT
FROM sys.dm_exec_sql_text(@sqltext)
GO

How to find percentage of a database backup job done in SQL Server?

Script to find percentage of a database backup job is done

SELECT percent_complete
 ,*
FROM sys.dm_exec_requests
WHERE command IN (
  'RESTORE DATABASE'
  ,'BACKUP DATABASE'
  )

Elapsed time in Hours to complete the Job

SELECT command
 ,percent_complete
 ,'elapsed' = total_elapsed_time / 3600000.0
 ,'remaining' = estimated_completion_time / 3600000.0
FROM sys.dm_exec_requests
WHERE command LIKE 'BACKUP%'

Tempdb is skipped. You cannot run a query that requires Tempdb

tempdb is skipped. You cannot run a query that requires tempdb

You Might have applied a patch that required the database server to be rebooted during a scheduled outage. The application on the web server might have connections cached to the database, which were invalid at that point. In the .Net implementation of connections, they only support the statuses Open and Closed, and Broken . So it boils down to the connection trying to use resources that are no longer there, but that doesn't explain this specific message. 

Solution: At this point the solution is to restart IIS/Reboot the box. 

Procedures

To restart IIS using IIS Manager

1. In IIS Manager, right click the local computer, point to All Tasks, then click Restart IIS.

2. In the What do you want IIS to do list, click Restart Internet Services on computer name.

3. IIS attempts to stop all services before restarting. IIS waits up to five minutes for all services to stop. If the services cannotbe stopped within five minutes, all IIS services are terminated, and IIS restarts. In addition, clicking End now forces all IIS services to stop immediately, and IIS is restarted.

What is IIS?

Definition - What does Internet Information Services (IIS) mean?

Internet Information Services (IIS), formerly known as Internet Information Server, is a web server producted by Microsoft. IIS is used with Microsoft Windows OSs and is the Microsoft-centric competition to Apache, the most popular webserver used with Unix/Linux-based systems.
Techopedia explains Internet Information Services (IIS)

IIS was initially released for Windows NT and, along with ASP (Active-Server Pages), finally made a Windows-box a usable alternative for web-hosting. That being said, it was also noted for being completely wide-open out of the box and required significant configuration to be made secure. 

This changed with later releases, and IIS is now generally considered by many to be a stable and usable product. As of 2011, the most current version is IIS 7, which includes pretty much all modern features you'd expect to see in a webserver, including tight integration to ASP.NET. Though, as with any Microsoft vs Linux debate, some would argue that Apache is the only way to go.

Exception deserializing the package "Access to the path is denied

ERROR:

TITLE: Microsoft Visual Studio
------------------------------

Failed to start project

------------------------------
ADDITIONAL INFORMATION:

Exception deserializing the package "Access to the path 'G:\VisualStudio\bin\Development\MySSISPackage.ispac' is denied.". (Microsoft.DataTransformationServices.VsIntegration)

------------------------------

Access to the path 'G:\VisualStudio\bin\Development\MySSISPackage.ispac' is denied. (mscorlib)

------------------------------
BUTTONS:

OK
------------------------------ 

Try this :

1. ExitData Tools
2. Open Task Manager and end any processes of DTSDebugHost
3. Delete the ISPAC file
4. Open Data Tools and rebuild the package

Is it is possible to Limit the number of ErrorLog Files in SQL Server

Identify SQL Server Error Log File used by SQL Server Database Engine by Reading SQL Server Error Logs

SELECT SERVERPROPERTY('ErrorLogFileName')

Is it possible to limit the number of errorlog files less then 6 ?

Nope. Minimum number to configure is 6. We can delete all the archived errorlog files from server if we want but  ensure they are not required for auditing purposes and such.

we can set it to 6 and then create a scheduled job that simply calls

EXEC sp_cycle_errorlog

The more often we run this, the smaller error log files will have and will keep it to 6 error log files.

 clean up message by reviewing what messages are appearing. For example if it is "successful backup" messages you can enable trace flag 3226 globally for that instance and this will suppress those messages from being written all the time. Outside of that it is up to us to determine what is writing the message and figure out how to clean that up. An example of this that is most common is "failed login" messages for rogue service or application on a remote server.pplication on a remote server.

CREATE FILE encountered operating system error 5 (Access is denied.)

CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file... (Microsoft SQL Server, Error 5123)


Try to open SSMS (Sql Server Management Studio) with Run as ADMINISTRATOR, and then try again to then try again to attach

For More info:  http://www.mssqltips.com/sqlservertip/2528/database-attach-failure-in-sql-server-2008-r2/

What is dbo in SQL Server?

dbo stands for DataBase Owner. It is a special database user that maps to the owner of the
database. When created, a database is assigned an owner, which is the login that created it.
You can query the database owner using one of the following queries:

SELECT SUSER_SNAME(owner_sid)
 ,NAME
FROM sys.databases;

-- or :
SELECT SUSER_SNAME(sid)
FROM sys.database_principals
WHERE principal_id = USER_ID('dbo');

This login is automatically mapped to the dbo special user, and thus is granted all
permissions in the database. You can change the owner of a database by using the
following command:

ALTER AUTHORIZATION ON DATABASE::marketing TO sa;

This will change the owner of the marketing database to the sa login.

SQl Server "How is My Memory Being used" Performance Counters"

Reference: http://www.quest.com/backstage/images/promotions/SQLServer-Perfmonance-Poster.pdf

AES algorith is stronger algorthm than 3DES/Triple-DES

AES is an algorithm adopted as a Federal U.S. government standard in 2002 and approved
by the NSA. It is a stronger algorithm than Triple-DES. When you choose the algorithm,
you need to balance between security and performances. AES_128 is gradually becoming
more vulnerable as new attacks are discovered but it is still considered reasonably safe. If your database contains classified
information, you should go for a bigger key, which is harder to crack. But the bigger the
key, the higher the performance impact will be. This being said, the performance impact
of TDE is relatively low.

Reference: Microsoft SQL Server 2012 Security Cookbook

Query to find total number of logical reads/physical reads done, when a given stored procedure is executed

SELECT ss.sum_execution_count

,t.TEXT

,ss.sum_total_elapsed_time

,ss.sum_total_worker_time

,ss.sum_total_logical_reads

,ss.sum_total_logical_writes

FROM (SELECT s.plan_handle

,SUM(s.execution_count) sum_execution_count

,SUM(s.total_elapsed_time) sum_total_elapsed_time

,SUM(s.total_worker_time) sum_total_worker_time

,SUM(s.total_logical_reads) sum_total_logical_reads

,SUM(s.total_logical_writes) sum_total_logical_writes

FROM sys.dm_exec_query_stats s

GROUP BY s.plan_handle

) AS ss

CROSS APPLY sys.dm_exec_sql_text(ss.plan_handle) t

WHERE t.TEXT LIKE '%PROCEDURE NAME HEREt%'

ORDER BY sum_total_logical_reads DESC



If you want to collect this data for a specific execution, you need to save the data before execution into a table, and then after execution read the DMV again to compute the delta. A presumptions is that there are no other executions of the procedure at the same time.

Rather than using dm.sys_exec_query_stats, you can use dm.sys_exec_procedure_stats, so that you get values on procedure level instead rather than on query level.

SQL Server 2008R2 Memory usage is High

It is pretty important to make sure you set the Max Server memory setting for SQL Server 2005/2008 to something besides the default setting (which allows SQL Server to use as much memory as it wants, subject to signals from the operating system that it is under memory pressure). This is especially important with larger, busier systems that may be under memory pressure.
This setting controls how much memory can be used by the SQL Server Buffer Pool. If you don’t set an upper limit for this value, other parts of SQL Server, and the operating system can be starved for memory, which can cause instability and performance problems. It is even more important to set this correctly if you have “Lock Pages in Memory” enabled for the SQL Server service account (which I always do for x64 systems with more than 4GB of memory).
These settings are for x64, on a dedicated database server, only running the DB engine, (which is the ideal situation).
Physical RAM MaxServerMem Setting
2GB 1500
4GB 3200
6GB 4800
8GB 6400
12GB 10000
16GB 13500
24GB 21500
32GB 29000
48GB 44000
64GB 60000
72GB 68000
96GB 92000
128GB 124000
If you are running other SQL Server components, such as SSIS or Full Text Search, you will want to allocate less memory for the SQL Server Buffer Pool. You also want to pay close attention to how much memory is still available in Task Manager. This is how much RAM should be available in Task Manager while you are under load (on Windows Server 2003):
Physical RAM Target Avail RAM in Task Manager
< 4GB 512MB – 1GB
4-32GB 1GB – 2GB
32-128GB 2GB – 4GB
> 128GB > 4GB
You can use T-SQL to set your MaxServerMemory setting. The sample below sets it to 3500, which is the equivalent of 3.5GB. This setting is dynamic in SQL Server 2005/2008, which means that you can change it and it goes into effect immediately, without restarting SQL Server.

-- Turn on advanced optionsEXEC sp_configure'Show Advanced Options',1;GO
RECONFIGURE;GO-- Set max server memory = 3500MB for the serverEXEC sp_configure'max server memory (MB)',3500;GO
RECONFIGURE;GO

-- See what the current values are
EXEC sp_configure;

You can also change this setting in the SSMS GUI, as you see below:

Finally, I have learned that it is a good idea to temporarily adjust your MaxServerMemory setting downward by a few GB if you know you will be doing a large file copy on your database server (such as copying a large database backup file).

Author:Glenn Berry
http://www.sqlservercentral.com/blogs/glennberry/2009/10/29/suggested-max-memory-settings-for-sql-server-2005_2F00_2008/



That's normal and by design.
SQL tries to keep everything in memory in order to better serve multiple requests for the same data.

Unless wetell it otherwise, SQL uses all the memory available.

On an 8 gig machine, we probably want to limit SQL to 7 gig, leaving 1 gig for the operating system. if we have other processes on that server besides SQL, you might want to limit SQL even more to accompdate some memory for those other processes.