SQL Server Admin 2014 :: CheckDB Running For Two Days

Jun 3, 2015

We carried out an in-place upgrade on our production server on Saturday - going from 2008 R2 to 2014.

We had tested this method out in dev/test and pre-production with only minor post issues to fix.

However, on production we had an issue whereby checkdb was hitting 100% CPU and caused overnight processes to hang. The checkdb statement was terminated and disabled by a colleague at 1 am.

Since then we have restored this database to a dev server and ran checkdb against it with no_infomsgs and all_errormsgs but it still hasn't finished since Monday morning!

The database is just over 800 GB and whilst checkdb was crippling the cpu, logical reads are less than one. However, sp_whoisactive is showing that it has done 56 million reads so far and this number increases periodically so it looks like it keeps going back to re-check the database with a deep dive.

Also, on a different environment, we ran check table statements and one of them took over 9 hours for a single table but came back clean (see attachment).

We need to wait for the output but the database is still in use in production and the mess will just get worse if it is indeed corrupted.

View 5 Replies


SQL Server 2014 :: Calculate Running Difference In Values Between Days?

Oct 13, 2015

I am trying to write a query to calculate the running difference between data on different dates. Below is what my table of data looks like. Basically I want to calculate the difference between the total_completed for each state and date.

08/27/15CA 19,952
09/11/15CA 26,336
10/02/15CA 35,444
10/08/15CA 38,278

below is what I am trying to achieve:

08/27/15CA 19,952 0
09/11/15CA 26,336 6,384
10/02/15CA 35,444 9,108
10/08/15CA 38,278 2,834

below is my code (I almost have what I need) I just can't figure out how show 0 as the completed_difference for the first Date for each state since there is no prior date to calculate against.

LEFT JOIN TOTAL_COMPLETED T2 ON T1.[Date] = T2.[Date] AND T1.States = T2.States


View 4 Replies View Related

SQL Server Admin 2014 :: Running Multiple Instances On 2 Nodes

Apr 12, 2015

I'm looking at installing 2008R2 and 2014 side by side, then using Mirroring to provide HA for the 2008R2 instance and AoHA for the 2014 instance. I'd be using the same two physical servers for both the Mirroring pair and the AoHA pair.

View 2 Replies View Related

SQL Server Admin 2014 :: Cannot Open Data File When Running Agent Job

Apr 30, 2015

I recently installed standalone version of SQL 2014 Standard on my work computer. I used Access before but I want to use a SQL server instead.

We have a shared drive that a file gets deposited every day at midnight. I want to be able to get this file and import it to the server (its basically a list of names).

Here what I have done so far:

I created the database

Created the file and successfully imported data into it using the Import Data feature.

I saved the SSIS package

Scheduled an Agent Job for this package to run at certain time,daily

At first the jobs would fail with a Access is Denied. I added a user under Credentials with my network account ( have admin rights on the work computer).Also added a Proxy for the Credential user I made.

Jobs fail with a “Cannot open data file” error. I tried changing things here and there, but I can’t get it to work.

View 9 Replies View Related

SQL Server Admin 2014 :: Print State Of Running Query To Output

Sep 21, 2015

I want print state of running query to output, because my query need long time to run. But print statement dos not work correctly!!

Note: I cannot use "go" in my query!

/* My Query */
print 'Fetch Data From Table1 is Running...'
insert into @T1
select x,y,z
from Table1
print 'Fetch Data From Table1 Done.'

[Code] .....

View 1 Replies View Related

SQL Server Admin 2014 :: Ensuring Upgrade Advisor Is Running Properly?

Oct 30, 2015

I have a SQL Server 2008 instance that is running on "LiveServer" our production database (ProdDB) - and we need to upgrade to 2014. In order to do some upgrade testing, I spun up a VM with the same version of SQL server on the test VM (TestServer), did a backup of the production DB from the live server, and restored it to TestServer under a different name (ProdDBUA).

I then installed SQL2014 Upgrade advisor onto TestServer, and ran it, checking all the boxes (reporting services etc..) and it all came back clean - no issues whatsoever - not a single warning even. I'm under the impression that stored procs/functions etc... all reside within the DB, so a backup will include those. Is that correct?

The problem is, I know I have stored Procs, functions and views that use deprecated joins in that LiveServer.ProdDB. What do I need to do/configure/check in order to make sure that the Upgrade Advisor is actually checking through all that T-SQL that has deprecated code? I want to have a list to give to my report writers of procs/functions/views that need to be rewritten prior to the upgrade going live.

If there is a modification that needs to be run on the TestServer.ProdDBUA, a cursor to change the path etc. DB is running in Compatibility mode 90.

View 4 Replies View Related

SQL Server Admin 2014 :: Running Frequently Transaction Log Backup During Integrity Check DB

Jul 6, 2014

Running Frequently Transaction logbackup during Integrinty check DB /optimization job will cause any issue /impact as duration will extend ...

View 2 Replies View Related

SQL Server Admin 2014 :: Multiple Database-collation Running On One Server?

Mar 30, 2015

We are consolidating some old SQL server-environments from 'OLD' to 'NEW' and one of our vendors is protesting on behalve of the collation we use on our 'NEW' SQL server.

Our old server (SQL 2005) contains databases with collation SQL_Latin1_General_CP1_CI_AS

Our new server (2014) has the standard collation Latin1_General_CI_AS

Both collations have CI and AS

From experience I know different databases can reside next to eachother on the same Instance.

The only problem could be ('could be !!') the use of TempDB with a high volume of transaction to be executured in TempDB and choosing for Snapshot Isolation Level ....

The application the databases belong to is very static, hardly updated, and questioned only several time per hour (so no TempDB issue I guess).

using different databases using a different collation running on the same instance?

View 5 Replies View Related

SQL Server Admin 2014 :: Does Security-admin Role Plus Deny Alter Any Login Cancel Each Other Out

Aug 27, 2015

I want to set up a database role so that users can use sp_readerrorlog through SSMS. It does a check on membership in the securityadmin role.

I have tested it and can see you can grant execute on xp_readerrorlog but the SSMS GUI uses sp_readerrorlog.

I thought I could create a user/certificate and add the signature to sp_readerrorlog but it's not permitted (likely because it's not a normal database object).

So the other solution is to add the users to the securityadmin role but then explicitly deny alter any login (best done with a custom server role in 2012+ but otherwise just manually in 2008). I tested this out and it works, I'm not able to alter any logins or increase my own permissions, I also did a check of what's reported from fn_my_permissions(null, null) and it shows minimal permissions like I'd expect.

View 0 Replies View Related

TempDB Log File Running Out Of Free Space While Running DBCC CheckDB On Large Database

May 28, 2015

In my environment, there is maintenance plan configured on one of the server and while running DBCC checkdb on a database of size around 200GB, log file usage of tempdb is increasing and causing the maintenance job to fail.

What can I do to make the maintenance job run successfully, size of the tempdb database is only 50GB and recovery model is set to simple. It cannot be increased as the mount point on which it is residing is 50GB.

View 3 Replies View Related

SQL Server 2012 :: Running Totals For Previous X Days?

May 13, 2015

get the desired results for the following sample data set. I was able to come up with a query that returns the the expected results however only for a given day, so I'd need to union several select statements the get the desired results which is definitely not ideal. I'd like to pass a parameter in (number of days) instead of doing a unions for each select.

SELECT 1 AS Id, 'A' AS Category, '2015-5-13' AS ActivationDate UNION ALL
SELECT 1, 'A', '2015-5-13' UNION ALL


View 9 Replies View Related

SQL Server Admin 2014 :: Encryption Key Not Known

Apr 14, 2015

I inherited a lot of Servers to upgrade to 2014 to include an SSRS Server.

The encryption Key was never backed up and it seems that no one knows what the password is?

Do I have to manually load the reports? There are a lot of Reports.


View 4 Replies View Related

SQL Server Admin 2014 :: BCP Aborts On First FK Violation

Sep 26, 2013

I want to use BCP to load data from a text file.

By default, constraints are turned off in bcp, so I use the CHECK_CONSTRAINTS hint.

bcp aborts if ANY of the rows contains a FK violation. No data get loaded.

So if I add the -b 1 batch size option, it loads all data UNTIL the first FK violation, but nothing after that.

I want to load EVERYTHING ... except for the violations. But bcp won't let me. Is there a way?

View 2 Replies View Related

SQL Server Admin 2014 :: What Is The Default SA Password

Jan 13, 2014

If I install an instance with Windows Only authentication, and then change it to Mixed Mode, if I enable the sa login, the password has already been set. What is the default? If it's generated, how secure is it? Is the password generated? What algorithm is used for that?

View 9 Replies View Related

SQL Server Admin 2014 :: How To Restore MDF File

Mar 21, 2014

My sql databases in SQL Server 2014 has the status "suspend" as I saw in SQL Management Studio. I can't restore to serviceable condition sql databases through standard procedures. I need to restore .mdf file.

View 9 Replies View Related

SQL Server Admin 2014 :: Return One Field / One Row

Jun 18, 2014

I am using a monitoring system where I can monitor a numeric SQL result assuming the result is one field and one row.I would like to do this to say monitor the free available space or percentage on say the Master database. DBCC SQLPERF gives me a few columns and results for all databases on the server.

View 2 Replies View Related

SQL Server Admin 2014 :: DNS Pointing To A Listener

Jun 25, 2014

In our environment applications are using a DNS name which points to the physical server ip address. Now we are planning to move to 2014. We are planning to have servers in different subnets so we will be having two ip adresses for listener. How we can point the DNS to the listener ips? If failover happens can the DNS point to the exact ip address of the listener where it's primary node?

View 1 Replies View Related

SQL Server Admin 2014 :: How To Schedule A Job To Run At Different Intervals

Jul 31, 2014

Is there a way to schedule a sql job to run at different intervals

For eg:
The job should run at
7:00 Am
8:00 AM
and then at 10:00 Am

View 3 Replies View Related

SQL Server Admin 2014 :: Non-yielding On Scheduler

Nov 19, 2014

We installed the Sql 2014 in Test server.

We observed frequently

"Process 0:0:0 (0x1e10) Worker 0x00000006B6D341A0 appears to be non-yielding on Scheduler 13. Thread creation time: 12906028806348. Approx Thread CPU Used: kernel 0 ms, user 0 ms. Process Utilization 13%. System Idle 84%. Interval: 70189 ms."

Is it better to run the profiler or performan counter?

What are the filters we have to select in the profiler to monitor the Sql server

View 0 Replies View Related

SQL Server Admin 2014 :: Reporting Services Through IIS?

Dec 8, 2014

I have a SQL server box running 2014 reporting services. I have another server running IIS v8.

I would like to be able to connect to the IIS site and be given the SSRS report browser.

So externally if I browse to [URL], I am presented with the report server interface, the same as if I browse to http://xxx.xxx.xxx.xxx/reports internally.

What are my options?

View 4 Replies View Related

SQL Server Admin 2014 :: DB Read Only Copy

Dec 11, 2014

What is the best approach for a read only copy of a database that is ~ 1TB. The primary database is fed nightly with an ETL process. We are currently trying to duplicate the ETL to read only server but that process is not going well. So we are looking at other options to let SQL make the copy.

The primary database is on a Win12R2 with SQL 12 or 14, a 2 node A/P failover cluster.

The read only copy will be on a Win12R2 with SQL 12 or 14. It is not a requirement to fail over to the read only copy if the primary should go down.

What would best the approach to accomplish the end result?

View 3 Replies View Related

SQL Server Admin 2014 :: Possible Errors In AlwaysOn

Jan 12, 2015

I did not worked on AVG in sql 2012, what are the possible errors and how to resolve in always on in sql 2012 .

View 1 Replies View Related

SQL Server Admin 2014 :: Script To Failover

Jan 18, 2015

I have 10 databases which are configured as principal in mirroring I need to failover all the databases as part of failover , instead of writing query each database as parner failover, is an script which will generate the databases as principal to failover ?

View 3 Replies View Related

SQL Server Admin 2014 :: Delay In Between Replicas In AG

Feb 18, 2015

How to check the delay in between replicas in AG?

View 3 Replies View Related

SQL Server Admin 2014 :: OST File Is Not Accessible

Feb 26, 2015

my 4 GB .OST file is corrupted and i am not able to access my mails and contacts.

View 2 Replies View Related

SQL Server Admin 2014 :: How To Identify The Job With The Code

Apr 7, 2015

I got query from sysproccesses and the result for one spid is : SQLAgent - TSQL JobStep (Job 0xF3BDA6DFF9DCF94D81FF97C49EDA7C61 : Step 1

Which job has this code : 0xF3BDA6DFF9DCF94D81FF97C49EDA7C61

View 1 Replies View Related

SQL Server Admin 2014 :: SSMS Will Only Run As Administrator

May 28, 2015

After installing SSMS on some computers - the only way we can get SSMS to run correctly is to run it as the administrator. Is there a way where you don't have to do that? These end users are logging as themselves and have accounts in SQL Server all set up - but SSMS will only launch for them if we right click and select "run as administrator".After doing some digging - it seems that this is a common problem out there.

View 3 Replies View Related

SQL Server Admin 2014 :: Old Backups Will Not Delete

Jun 19, 2015

Have a SQL 2014 install and cannot for the life of me get the maintenance plan to remove old backups. I've tried everything. Rights to the folder where the backups are stored are adequate, extension set in the clean up task is as it should be, etc. Log shows the job ran successfully. Running the command manually shows successful completion, but backups are still not removed.

View 9 Replies View Related

SQL Server Admin 2014 :: What Does Restore Log Do After The Log Is Restored

Jul 29, 2015

when execute the restore log command, in the messages window it shows how many seconds the restore takes, at the meantime, on the status bar, it also shows the seconds the command takes.

Two values are different and could be very different, please see below examples , restoring takes 1.8 seconds, but in total the command takes 4 seconds to complete, the other one is 8.1 seconds and 12 seconds.

What does SQL Server or Windows do after the restoring?

pic a:

pic b:

I did a xperf, I can see after the restoring is completed, sql server did garbage collect and log write, which just run very quickly, but storage is busy on reading the log file for nearly 2.2 seconds( 4-1.8), and 4 seconds ( 12-8.1) .

pic 1:

pic 2:

see pic 1 above, from 13 to 17, the restore operation is finished, but the storage jump to 100% active to do some reads, only reads no writes. zoom that period shows pic 2, it read 4096 (I don't know the unit size) for about 4 seconds, what does this do?

Data file, log file, backup file are no different drives, but all local drive, the interesting point is the read jumped after restoring, I tested it on different server, same result...

View 1 Replies View Related

SQL Server Admin 2014 :: Moving Databases From One PC To Another?

Aug 9, 2015

I've got an old version of SQL Server 2008 R2 Developer Edition on an old PC which is failing. I've got a new PC and have put SQL Server 2014 Developer Edition onto it. Now before the old machine completely dies, I've gotten into SSMS on the old machine and did a backup of the databases I want to save. I've moved the .BAK files to where I could get to them from SSMS on the new machine. I've gotten into SSMS and tried to do a restore the database to my new machine. However I'm getting an error that does not make any sense to me.

The database I'm I've backed up is named JobSearch. When I backed it up, that was the only database I had selected. Like I said I copied the .BAK to the new machine. Got into SSMS, told it that I wanted to restore the JobSearch database, telling it where I wanted to put it, and it then immediately fails with a:

"Restore of database 'JobSearch' failed. System.Data.SqlClient.SqlError: Logical file 'VideoLibrary_Data' is not part of the database 'JobSearch'. Use RESTORE FILEISTONLY to list the logical file names."

Well of course VideoLibrary isn't "the logical file". But neither did I select VideoLibrary (which is a database I also want to move, but I'm doing one at a time). So what in heck is going on here? Why is it complaining about a database I haven't even selected to back up? Why, when I check everything on the old machine, it's backing up JobSearch, but on the new machine it sees VideoLibrary?

View 6 Replies View Related

SQL Server Admin 2014 :: Add New Column To A Table

Aug 24, 2015

I am new in SQL Server, What to check ,what action do i need to take while adding a new column to a table.

View 5 Replies View Related

SQL Server Admin 2014 :: TempDB Is Full

Oct 6, 2015

Message: Executed as user: NT AUTHORITYSYSTEM. The transaction log for database 'tempdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases [SQLSTATE 42000] (Error 9002). The step failed in my sql server agent job i have the above error, this type of errors i got some of multiple jobs.

View 9 Replies View Related

SQL Server Admin 2014 :: Log File Growth

Nov 7, 2015

A log file size of a production database has been increase from 4gb to 150 gb initial size.Now i want to find when it will grow & how much it grow & which transaction is responsible for this.

View 6 Replies View Related

Copyrights 2005-15 www.BigResource.com, All rights reserved