Thursday, August 15, 2013

[SQL 2012] Sp to create SQL job

[SQL 2012] Sp to create SQL job

Sp to create SQL job

Posted: 15 Aug 2013 12:52 AM PDT

After my failed attempt last time, I thought of a new way to get my scheduled backups in place.I have basically created an SP and put it in model, this creates the folder structure for the backups, deletes old backups then takes a full backup of the database. To automate this, I am created another SP, which I will run, feeding in the database name of which I Want it to schedule backups for.create procedure dbo.CreateBackupAgentJob @DatabasenameInput varchar(100) = null as declare @JobCode varchar(max)declare @thisserver nvarchar(100)select @thisserver = (select @@SERVERNAME)set @JobCode = 'begin transactionDECLARE @jobId BINARY(16)EXEC msdb.dbo.sp_add_job @job_name=N''' + @DatabasenameInput + '- backup Job'', @enabled=1, @notify_level_eventlog=0, @notify_level_email=2, @notify_level_netsend=2, @notify_level_page=2, @delete_level=0, @category_name=N''[Uncategorized (Local)]'', @owner_login_name=N''sa'', @job_id = @jobId OUTPUTselect @jobIdGOEXEC msdb.dbo.sp_add_jobserver @job_name=N''' + @DatabasenameInput + '- backup Job'', @server_name = @thisserverGOUSE [msdb]GOEXEC msdb.dbo.sp_add_jobstep @job_name=N''' + @DatabasenameInput + '- backup Job'', @step_name=N''' + @DatabasenameInput + ' Backup'', @step_id=1, @cmdexec_success_code=0, @on_success_action=1, @on_fail_action=2, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N''TSQL'', @command=N''exec dbo.backupdatabase'', @database_name=@databasenameinput, @flags=0GOUSE [msdb]GOEXEC msdb.dbo.sp_update_job @job_name=N''' + @DatabasenameInput + '- backup Job'', @enabled=1, @start_step_id=1, @notify_level_eventlog=0, @notify_level_email=2, @notify_level_netsend=2, @notify_level_page=2, @delete_level=0, @description=N'', @category_name=N''[Uncategorized (Local)]'', @owner_login_name=N''sa'', @notify_email_operator_name=N'', @notify_netsend_operator_name=N'', @notify_page_operator_name=N''GOUSE [msdb]GODECLARE @schedule_id int[color=#ff0000]EXEC msdb.dbo.sp_add_jobschedule @job_name=N''' + @DatabasenameInput + 'backup Job'', @name=N''10pm''',[/color] @enabled=1, @freq_type=4, @freq_interval=1, @freq_subday_type=1, @freq_subday_interval=0, @freq_relative_interval=0, @freq_recurrence_factor=1, @active_start_date=20130815, @active_end_date=99991231, @active_start_time=220000, @active_end_time=235959, @schedule_id = @schedule_id OUTPUTselect @schedule_idGO )exec @jobcodeI am getting a syntax error on the highlighted row? please help :D

always on sql server 2012 Hybrid Storage

Posted: 14 Aug 2013 06:25 AM PDT

Good afternoon,What are the implications for the always on, have the primary with disk SSD and secondary with HDD SAS?thank you very muchMichael Antunes

Clear cache after big job?

Posted: 14 Aug 2013 06:02 PM PDT

Hi everyone,We have several big jobs which execute a lot of SQL. These jobs execute every day during the night and take some time to finish.I suppose that all those queries are then stored in SQL Server cache right?Also, we have a website which uses this SQL Server database.Possible problem is, queries in jobs and website queries are different. So, agent jobs are used in back end to set up products, categories, brands etc. Web pages are using other procedures and other queries on front end to present products, enable online shopping etc.I am thinking, maybe it would be good to clear cache after jobs are finished? Maybe by using:[code="sql"]DBCC FREEPROCCACHE WITH NO_INFOMSGS;[/code]I am worrying that cache is not used on best way after jobs are finished. During the day, I need it optimized only for website queries. I suppose that jobs queries take some resources in cache. As a consequence, website would run slower?So, idea is, during the night, after jobs are finished, add as a last step clearing of cache. Then, website queries will build cache again and website will work as fast as possible during the work hours. On this way jobs queries don't affect cache. All cache belongs to website queries.Please let me know your thoughts :-)Regards

checkdb and snapshots

Posted: 14 Aug 2013 07:13 AM PDT

Hi,The system is SQL Server 2012 SP1 Enterprise Ed 64-bit 11.0.3349.0Integrity Check has been failing on random user databases and on master and msdb, the error is:[font="Courier New"]Msg 5030, Level 16, State 12, Line 1The database could not be exclusively locked to perform the operation.Msg 7926, Level 16, State 1, Line 1Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.[/font]The above error is generated for Integrity checks that were ran from a scheduled job or directly in Management Studio, and also when dbcc checkdb is ran WITH NO_INFOMSGS or WITH TABLOCK.None of these messages are logged in SQL Server Error Log, and I did not find any other messages occurring at the same time in the log or in Event Viewer.Tried creating a snapshot but failed, error message:[font="Courier New"]Msg 1823, Level 16, State 2, Line 1A database snapshot cannot be created because it failed to start.Msg 5119, Level 16, State 1, Line 1Cannot make the file "F:\MSSQL\Data\ReportServer_0814.ss" a sparse file. Make sure the file system supports sparse files.[/font]If this is a permission issue, how come the integrity check is successful for some of the databases?Any suggestions?thank you!

Run a script file against Multiple databases

Posted: 14 Aug 2013 05:18 AM PDT

I have a script file in C:\Script.sql that I need to run against multiple databases.I can run on one database as follows :r c:\Script.sql But my challenge is I need to run on all the databases returned by the folowing query. The script file creates abount 50 objects .SELECT db_name from Table1 where dbID > 4 Thanks

Restoring a database and simultaneously increasing it's file size

Posted: 14 Aug 2013 03:43 AM PDT

Hey guys, At my organization, we have just built a new SQL Server machine to which we would like to migrate existing databases from other servers. For one of the databases, we'd like the MDF and LDF to have a new initial file size once it is moved. What is the best way to accomplish this? Is there a way to increase the file sizes during restoration from a backup? Or will we have to restore and than alter the file size through T-SQL (or SSMS)? The reason we are asking, is because we want to ensure that the MDF and LDF files on the new server are contiguous in the file system, and we are worried that increasing the file size after restoration will lead to having non-contiguous files in the file system. If there is more detail I can provide, just let me know.Thanks,

SAS disk partitioning for SQL Server

Posted: 14 Aug 2013 06:31 AM PDT

Good afternoon,I have 6 SAS HDD with 900GB capacity each. What better way to split it to SQL Server, considering LDF, MDF and TempDB. For four database of volumetric total of 180GBthank you very muchMichael Antunes
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog