How to add Date and Time to output file for Jobs

In one of my project I  come across this requirement that setup a job to run every hour and the output must have a Date and Time.

I found couple of solutions

1. When using SQLServer Management Studio(SSMS) you can add below syntax for output file

SSMS--> SQL Server Agent --> Job Name--> Properties --> Steps --> Select "edit" -->  Advanced 

$(ESCAPE_SQUOTE(DATE))_$(ESCAPE_SQUOTE(TIME))

For exL:-
D:\SQLBackup\ouput_file$(ESCAPE_SQUOTE(DATE))_$(ESCAPE_SQUOTE(TIME)).txt

2.  Use Tokens in Job setup

Refer below MSDN doc
http://msdn.microsoft.com/en-us/library/ms175575.aspx

3. use %date% format with sqlcmd

for ex:-
sqlcmd -S sqlserver,port -U <user> -P <password>  -i "c:\query.sql" -o "c:\ouput_%date%.txt"

You can play around %date% format if you are looking for specific date format.

Regards
Satishbabu Gunukula

How to find the Data and Log File location of all the Databases

I received a SCOM space alert for one of my SQL Server 2008 instance and found that that one of my Data Drive is almost full.

D: disk is at or near capacity. You may need to delete some files

I know that databases are not growing that fast. I released some space by clear the log files, but it didn’t help much.

When I looked into the data and log file folders I see log of files with very old date. It looks like somebody got deleted the database but never cleared the files.

In this situation make sure you first get a list of data and log files with location using below SQL Query then proceed with cleaning the old files.

SELECT Name,Physical_Name AS File_Location
FROM sys.master_files

To find disk space allocate and fee space in SQLServer use below command.

SELECT Name, Filename,

CONVERT(Decimal(15,2),ROUND(a.Size/128.000,2)) [Currently Allocated Space (MB)],

CONVERT(Decimal(15,2),ROUND(FILEPROPERTY(a.Name,'SpaceUsed')/128.000,2)) AS [Space Used (MB)],

CONVERT(Decimal(15,2),ROUND((a.Size-FILEPROPERTY(a.Name,'SpaceUsed'))/128.000,2)) AS [Available Space (MB)]

FROM dbo.sysfiles a (NOLOCK)

The above SQL provided Name of the Data and log file, File name with Location, Currently Allocated space, Space Used and Available space in SQL Server.

Regards

Satishbaub Gunukula

http://www.sqlserver-expert.com

Error Code: 0x80071398 in SQLServer

Recently we are performed the storage migration of SQLServer 2008. After migration when we are trying to failover the instances we received below error for one the instance.

Error Code: 0x80071398" The operation failed because either the specified cluster node is not the owner of the group, or the node is not a possible owner of the group.

If you read the error it is easy to identify the issue. The node that you are trying to failover may not be the possible owner of that instance.

Possible solution: To fix the issue go to Failover Cluster Manager à Select the instance à go to properties à you should select the NODE in the preferred owner’s List

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com

There is insufficient system memory in resource pool 'default' to run this query

We have encounter “insufficient memory” issue on SQLServer cluster and found below error in Event Viewer and Error.log

2014-04-04 05:36:16.83 spid136 Error: 701, Severity: 17, State: 123.
2014-04-04 05:36:16.83 spid136 There is insufficient system memory in resource pool 'default' to run this query. 

Below are the main reasons for the issue

1. The physical memory is completely used and not available for SQLServer
2. SQLServer engine Max memory allocation has been reached limit.
3. Virtual memory is full

First find out which processes are consuming memory, if any tools or application processes outside of sqlserver consuming and then you can close or kill the process, if not important.

Run below command to find out memory Status

DBCC MEMORYSTATUS

You can also run below commands to clear the memory

DBCC FREESYSTEMCACHE
DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

Possible workaround/solution:

If the physical memory on the server is full and you have allocated “Maximum Server memory” for SQL Server close the physical memory of the server then I would recommend decreasing the “Maximum Server memory” of SQL Server to left some memory for Operating system.

If your SQLServer engine max server memory is reached the limit and you still have physical memory available then I would advise increasing the “Maximum Server memory” will help.

If your Virtual memory is full then you need more Physical memory and I would advise to add the same.

Regards
Satishbabu Gunukula
http://www.sqlserver-expert.com

Error:18456, Severity:14, State:58 in SQLServer

Users may encounter SQL Server login failures with below error message.

Cannot connect to “SQL Server\Instance”
Additional Information:
Login failed for user “TestUser”.(Microsoft SQL Server, Error: 18456) 

User is able to connect using Windows credentials, but not able to connect using “SQL Server Authentication” using SQL Server Management Studio and ODBC.

Let’s see what is in the ERROR.LOG

2014-01-28 23:06:46.80 Logon     Error: 18456, Severity: 14, State: 58.
2014-01-28 23:06:46.80 Logon      Login failed for user TestDB. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: xx.xx.xx.xx]

Cause: The server is configured for Windows authentication only and not able to connect using SQL Server Authentication.

Solution: Modify the Server is to use both SQL Server and Windows Authentication mode.

Right Click on Instance -- Go to properties -- Click on security – change to “SQL Server and Windows Authentication mode” – Restart the Instance

After SQL Server instance restart you should be able to connect. Sometimes users may see below error

provider: SQL Network Interfaces, error: 28- Server doesn’t support requested protocol) (Microsoft SQL Server 

Follow the below link to resolve the issue
http://www.sqlserver-expert.com/2014/01/cannot-connect-to-sql-server-or-instance.html

Thanks,
Satishbabu Gunukula
http://www.sqlserver-expert.com