Query to takequtomated table backup

Query to take backup of tables randomly

 

declare @sqlquery nvarchar(4000)

 

set @sqlquery = N'select * into niku.[CMN_SEQ_CMN_EXCHANGE_RATES_bk_' + convert(varchar(8),GETDATE(),112) + '] from niku.[CMN_SEQ_CMN_EXCHANGE_RATES]'

print @sqlquery

execute sp_executesql @sqlquery

query to find the last backup taken

Here is the query to find the last back taken in SQL Server

SELECT
 T1
.Name as DatabaseName,

COALESCE
(Convert(varchar(12), MAX(T2.backup_finish_date), 101),'Not Yet Taken') as LastBackUpTaken,

COALESCE
(Convert(varchar(12), MAX(T2.user_name), 101),'NA') as UserName

FROM
sys.sysdatabases T1 LEFT OUTER JOIN msdb.dbo.backupset T2

ON
T2.database_name = T1.name

GROUP
BY T1.Name

ORDER
BY T1.Name

SQL server login timedout expired

I was unable to login to SQL server : It was throughing an error ,Login timed out expired:

i was  able ping the server, i was able to RDP the server and SQL services were running fine but i was not able to register SQL in SSMS or SQLWB.

I found the problem was with Firewall it seems to be blocked or was not enabled for port 1433.

So i have created an inbound rule for Sql to allow 1433 port for whole domain in Windows firewall advance security.

--> go to Adminstrative tools--> select windows Firewall with advance security ----> create one inbound rule  --> select port and click on next-->select TCP and port number 1433-->select allow connection-->select only to the domain --> Type Name and click on finish .

This fixed my issue and i was able to register  SQL server.

 

SQLAgent.out and SQLagent error log files has filleup the drive

Recently i faced an issue, SQL Agent files (SQLAgent.out,SQLAgent1,SQlAgent2,SQlAgent3,SQlAgent4,SQlAgent5,SQlAgent6,SQlAgent7) has occupied almost whole C Drive(40 GB), so No one was able to RDP and no memory was available and no users were able to run queries or perfrom any transactions.

Ans:

Please make sure take the backup error log files before if you need this for reference in future.

What is the SQL Server Error Log?

SQL Server maintains its own error logs that contain messages describing informational and error events. The SQL Server error log is a great place to find information about what is happening on your database server. Each SQL Server Error log will have all the information related to failures / errors that has occurred since SQL Server was last restarted or since the last time you have recycled the error logs.

By default, there are six achieved SQL Server Error Logs along with the ERRORLOG which is currently used. However, it is a Best Practice to increase the number of SQL Server Error Logs from the default value of six. In this tip, you will see the steps which you need to follow to increase the number of SQL Server Error Logs.

A new SQL Server error log file will be created when one of two things happens:

1. The SQL Server service is started
2. sp_cycle_errorlog is called

Best Practice – SQL Server Error Log

1. Ensure you Error log directory is backed up regularly, with windows OS backup
2. Increase the number of SQL Server Error Logs from the default value of six.
3. Make a schedule job to Recycle the Error on a specified schedule to ensure, you log size in control as large files takes time to read data
4. Check SQL Server Error log on daily basis as all important / critical messages and warnings are logged in SQL Server Error Log

How to specify number of log files to be maintained by SQL Server

SQL Server Management Studio -> Management -> SQL Server Logs
Right click -> Configure

Same way, we can configure the number of SQL Server Agent Logs too.

SQL Server Management Studio -> SQL Server Agent-> Server Logs
Right click -> Configure

How to Start a New SQL Server Error Log

sp_cycle_errorlog, stored procedure is used to recycle the SQL Server error log

How to Start a New SQL Server Agent Log

USE MSDB
GO
EXEC dbo.sp_cycle_agent_errorlog
GO

OR, Agent Log can be cycle graphically also

Notes

1. By default, SQL Server maintains a minimum of 6 Error Log Files.
2. Each time the SQL Server is restarted, the Current Active Log File is recycled and new one created.
3. The Error Log Files are stored in the “Microsoft SQL Server\MSSQL.1\MSSQL\LOG\” Directory.
4. It stores, security related login information, Logins info such as Failure Logins or Failure and Success Logins
5. Error Logs also stores, changes in Database Settings, Database backup related information; both successful backups and failures are reported.
6. If case SQL Services failed to start, SQL Server Error Log is the first thing, which shuld be looked at to figure out the reason, why its failing.
7. sp_cycle_errorlog, can only be executed by members of sysadmins

 

To automate this process across servers, reference the sp_set_sqlagent_properties system stored procedure.  Below outlines a sample execution.

USE [msdb] GO EXEC msdb.dbo.sp_set_sqlagent_properties @errorlogging_level=7 GO