Thursday, February 28, 2013

[MS SQL Server] Applying checkpoint

[MS SQL Server] Applying checkpoint

Applying checkpoint

Posted: 28 Feb 2013 04:03 AM PST

Hi,Do you need to have access to master in order to apply checkpoint in your query? Please advise. The login I am using has db_owner rights...not mapped to master.....is this good enough to run checkpoint?SueTons.

Strange error, cannot script out jobs from SQL.

Posted: 28 Feb 2013 04:01 AM PST

When trying to script out a current job on our MSX sql server, we receive this error,"Unable cast object of type 'system.dbnull' to type 'system.string'. (microsoft.sqlserver.smo)"I cannot find a lot of information on this topic. what would allow this job to run, yet prevent it from being scripted out and moved to our other MSX server?

Sudden slow performance from distributor to subscriber in transactional replication?

Posted: 27 Feb 2013 09:35 AM PST

We've had transactional replication enabled for 6-8 months to update a secondary database instance that's used for reporting purposes. It's a single subscriber, set to pull.At some point over the last few days the subscriber apparently stopped being able to keep up with the replicated data, and now I'm sitting on anywhere from 2-3 million undistributed commands at any given point. Stuff is making it from the distributor to the subscriber, just very slowly apparently.The problem of course being that I can't find any reason for this sudden change. Nothing has changed from a network/SAN/server perspective that we can find, there's no errors in the SQL or SQL agent logs, and windows event logs are also quite boring. These servers are connected via multiple 10GBe links to a core switch, and our networking gear looks pretty bored, same deal on our SAN.If I spend some time sifting through the commands in the distributor db, nothing really crazy jumps out at me other than the sheer volume of commands sitting in there. If I look on the subscriber all I see are updates being applied without any blocking or anything unusual going on there. If I insert a trace token, latency from Publisher to Distributor is 1-2 seconds, while Distributor to Subscriber has still been pending after around 7-8 hours. I've tried restarting the various SQL agent jobs, nothing really changes there.Any suggestions on what to look at? I'm running out of ideas here.

Multiple Transaction Logs, will removing one break Log Shipping?

Posted: 27 Feb 2013 06:45 AM PST

I have a production server that a former DBA created additional LDF files for a given database - it is quite small (5GB)The database itself is about 100GB PRIMARY file group, a 55 GB Index file group, and it's primary log file sitting at about 50GB, then they created the smaller 5GB log file on a different drive.I'd like to remove this smaller log file altogether by issuing [code="sql"]USE MyDB;DBCC SHRINKFILE (N'MyDB_log' , EMPTYFILE);IF @@ERROR = 0BEGIN ALTER DATABASE MyDB REMOVE FILE MyDB_log;END[/code] Will this work or will it break my existing log shipping configuration? My guess is that I'd be okay but just want to be certain.Any recommendations from the experts?

Does the adrenaline rush ever subside when rebooting a DB server.

Posted: 27 Feb 2013 06:04 AM PST

I work in a small shop as the accidental DBA and for the first time rebooted our production DB server after applying CU 9 to fix some issues we were having. It went fine but until the server booted up and sql started I had that adrenaline rush of what happens if ... Does that ever get better when you have to do something to a Major point of failure (We have a backup server but it would take several hours to get up and running again).This time every thing went fine and i had proper backups but it was still nerve racking.

Job Execution Details

Posted: 28 Feb 2013 12:03 AM PST

Hi Experts,One of our maintenance job which was not supposed to run on weekdays executed 3 times today. Job history is showing its invoked by system account .Is there any way we can figure out how the job started??TIA

sysfiles and master_files out of sync

Posted: 27 Feb 2013 09:38 PM PST

Hi all,I apologise if this has been raised before, but I couldn't find it using search...I've come across a situation on one of my servers where the entries in sysfiles and master_files are out of sync for the master database.As far as I was aware this is a situation that should not be able to happen, and all tests I've done on my dev server with changing the logical file name (admittedly not on the master database) have failed to recreate it.Here is some TSQL I'm using:[code="sql"]use master;go--reports master log file logical name as 'master_log'select name, fileidfrom sysfilesorder by fileid;--reports master log file logical name as 'mastlog'select name, [file_id]from sys.master_fileswhere database_id = db_id()order by [file_id];select FILEPROPERTY('master_log', 'SpaceUsed');select FILEPROPERTY('mastlog', 'SpaceUsed');[/code]and the result sets I'm seeing:[code="other"]name fileid---------- ------master 1master_log 2name file_id---------- -----------master 1mastlog 2----------68----------NULL[/code]Server is Win2k8 with SP1, and SQL Server instance is 2k8R2 with SP1.Any help and/or advice around how things could of got in to this state would be much appreciated!Russell

SQL Database code comparison

Posted: 27 Feb 2013 05:21 AM PST

Good Day all,I am in a process of setting up a full blown test and development environment. We are primarily an ASP.NET C# shop. I will start using Team Foundation Service for my code and source control. I am wondering on a good approach to manage SQL changes such as: Table changes, Stored procedure changes,Function changesIs there software that can track changes between 2 databases? I understand that you can track changes within TFS but I have some doubts about that… Anyways any input is greatly appreciated.

delete a large amount of rows in a table

Posted: 27 Feb 2013 05:18 AM PST

Please help me make this query more efficient because i been waiting for more then 1 hour for query to complete and it did not finish and i got like 25 large tables to delete from:this is what i wrote it like but it is very slow ..SELECT * INTO WORKTBL FROM [CHARACTER_01_DBF].[dbo].SKILLINFLUENCE_TBL WHERE m_idplayer NOT IN (SELECT DISTINCT(m_idPlayer) COLLATE DATABASE_DEFAULT FROM [LOGGING_01_DBF].[dbo].[LOG_LOGIN_TBL] WHERE End_Time > '20130123015613')DROP TABLE [CHARACTER_01_DBF].[dbo].SKILLINFLUENCE_TBLSELECT * INTO [CHARACTER_01_DBF].[dbo].SKILLINFLUENCE_TBL from WORKTBLDROP TABLE WORKTBLDo you know any better method ? much faster then hours and hours of waiting for it to complete ?
Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog