I have a server with 2 processors, 12 cores each making it a 24 core logical system.
My tempdb has 4 datafiles and 4 logfiles created.
I know creating more number of log files in tempdb may not make sense, but how many tempdb data files are supposed to be created?
Should it be 24?
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 RelatedIs there a formula for calculating how expensive a transaction will be in terms of disk space used before its run. I dont want it accurate to the MB, but rough enough so I can determine how much additional space to assign to a transaction log or SAN volume.
Currently we're reindexing ~25billion rows, nothing too wide, say 12 columns consisting of 1 varchar(50) and the rest ints, bits and money.Roughly speaking if I reindex the clustered index on an int indetity, (with sort_in_tempdb) how would I calculate the the disk space used?
I want to use a certificate with 10-years expiration date. I created a new template certificate, based on default computer certificate, and I only change expiration date to 10 years.
In SQL Server SSL configuration, I dont view this certificate. In only view certificate created with the "Computer" template (with validity period of 365 days). All others certificates based on template are invisible to SQL Server
How can I use a custom template instead of the default "computer" template ?
What I asked for: Three Windows Server 2012 R2 machines with independent storage running a SQL Server 2014 AlwaysOn Availability Group. DB1 would be the primary, DB2 would be a synchronous replica, and DB3 would be a remote asynchronous replica.
What I was given: a two-node Windows Server 2012 R2 WSFC to run SQL Server 2014 Enterprise with shared storage and a third (remote) Windows Server 2012 R2 machine with independent storage, also with SQL Server 2014 Enterprise, to host an AlwaysOn Availability Groups asynchronous replica.
DB1 and DB2 (as Cluster1) share an E: drive. The remote DB3 has its own E: drive. Initially, DB3’s E: drive was claimed as a cluster resource and I couldn’t even see it. I’ve had several ugly days trying to make this work and have temporarily given up, installing DB3 as a standalone SQL Server that is no longer part of the WSFC and pointing everything towards that (it was originally a third node in the WSFC).
Is it possible to create an AlwaysOn Availability Group with nested clusters (i.e. create the AOAG with Cluster1 and DB3 and somehow ignore the individual nodes that comprise Cluster1)?
I have question about tempdb needs to be configured 100GB 64kb block size.its fresh installation.
how to allocate the file sizes.still im not sure how many log files needs to be created with 100GB equals to 64KB block size.
what is 64KB block size and how to divide the logfiles 64KB into the 100gb or 50GB?
what is 64 KB cluster has 128 sectors?
tempdb drives should be formatted with a 64K allocation? how many files needs to created for good performance with 50GB or 100GB? ot 1TB
I was trying to configure maintenance plan to take nightly full database backup and Log backup. I was trying to configure it like in attached file. Any links that i can follow and configure as in attached file.
View 7 Replies View RelatedI am looking for a table where Maintenance Clean Up Task configuration is stored. For example, Delete file older than the following - which is 2 days. Which table can I retrieve the setting in msdb ?
View 5 Replies View RelatedI'd like to capture the change event when it happens and have both a record posted to a table and an email notification to the DBA.
View 7 Replies View RelatedI proposed on a new server that we separate Data Files, Log Files, tempDB, Backups, etc. onto separate LUNS on a SAN with High Speed Solid State Drives.I was told that with the new technology with solid state SAN's that it would decrease performance and that it did not work the same way as it did when you had RAID 5's etc.I thought that if things were cared out correctly by a SAN Administrator they would know how to configure for optimal performance.
View 2 Replies View RelatedI 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.
Here with i have attached the execution plan, In that i have tempdb spill over in sort operation. i have created index to sort even it needs temp db spill over. [URL]....
View 9 Replies View RelatedI've been trying to make the following query more performant by breaking it up into smaller pieces.
SELECT MT.A3+MT.A4 AS A34,MT.A3
-- ,M.*
FROM
Master_TAB M
JOIN (SELECT M.A1,t3.A3,t3.A4,M.A6,M.A2,ROW_NUMBER() OVER (PARTITION BY A1,A6,A3,A4 ORDER BY A5 DESC) AS rownum
[Code] ....
I know that the Spill is caused by the Sort but I can't remove the sort (sort can't be done in front end). My master table had 1.7 million rows and almost 200 columns (bad design? I know but can't be changed as there's too much that would be affected) every row is little over 1KB
Here's my attempt...
-- MASTER_TAB has 1.7 million rows and 50 columns
CREATE TABLE [dbo].[tmp_ABC](
[A1] [varchar](13) NOT NULL,
[A2] [varchar](5) NOT NULL,
[A3] [varchar](4) NOT NULL,
[A4] [varchar](4) NOT NULL,
[A5] [int] NULL
) ON [PRIMARY]
[Code] ...
This is the Query that is causing the Spill (in reality I'm supposed to bring back all 200 columns fro the master table but for debug purposes I limited the columns)
Select c.A3+c.A4 as A34, c.A3, c.A1
-- M.*
from tmp_DEF c
join MASTER_TAB M on M.A1 = c.A1 and M.A2 = c.A2
order by c.A3, C.A4
if I just run the following I get no spill:
Select c.A3+c.A4 as A34, c.A3, c.A1
from tmp_DEF c
order by c.A3, C.A4
as soon as I add the Master table as a Join I get the Spill...
I read many articles, tried many suggested things (creating indexes... clustered, non-clustered) without success. Maybe I'm totally in Left Field and should enhance the performance going another route?
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.
[URL]
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?
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 RelatedMy 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 RelatedI 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 RelatedIn 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 RelatedIs 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
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
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?
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?
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 RelatedI 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 RelatedHow to check the delay in between replicas in AG?
View 3 Replies View Relatedmy 4 GB .OST file is corrupted and i am not able to access my mails and contacts.
View 2 Replies View RelatedI got query from sysproccesses and the result for one spid is : SQLAgent - TSQL JobStep (Job 0xF3BDA6DFF9DCF94D81FF97C49EDA7C61 : Step 1
Which job has this code : 0xF3BDA6DFF9DCF94D81FF97C49EDA7C61
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 RelatedHave 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 Relatedwhen 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...
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?
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