I looked at the number of VLFs in ony databases and figured out it was 300. I looked at the log file and its 40GB with 99% free space. I reduced the log file to 1GB. Now I looked at the VLFs and the count is 250. so 250 vlfs in a 1GB of log file. Is there a way to get the VLFs to a decent number?
Also my transaction log backups run once every 5 mins and auto growth on data and log files is 10MB.
I have a table with 99.9% of unused size. How do I reduce the size?I have tried those commands below but they do not work.
ALTER INDEX [VBDATA~0] ON qa2.VBDATA REBUILD
DBCC CLEANTABLE (QA2,"qa2.VBDATA", 0)
WITH NO_INFOMSGS;
GO
ALTER INDEX ALL ON qa2.VBDATA REBUILD
I'm worhing with SQL Server7.0 and I have created a database.
I have objets and data in my database
My database have 300Mb and I only need about 50.
How can I reduce to 50 the dimension of my database.
Is it posible??
Any advice will be greatly apreciatted.
Nauj
I have heard that high numbers of VLF's aren't good. It can impact performance and can delay recovery time, so I wanted to test that.
I created 2 DBs with 100MB datafile and 50MB logfile.
TestDB log file had 100MB autogrowth
TestDB2 log file had 1% growth.
I inserted 1048576 records, took the backup
Ran DBCC loginfo and
TestDB had 40 VLFs and
TestDB2 had 165 VLFs
But when I restored both DBs, this is what I got.
TestDB:
RESTORE DATABASE successfully processed 42258 pages in 4.420 seconds (74.691 MB/sec).
SQL Server Execution times:
CPU Time = 125ms, elapsed time = 8323 ms.
TestDB2:
RESTORE DATABASE successfully processed 42257 pages in 3.943 seconds (83.724 MB/sec).
SQL Server Execution Times:
CPU time = 109 ms, elapsed time = 8314 ms.
Question is: Where is the difference? How TestDB which has 40 VLFs are better than TestDB22 which has 165 VLFs.
We have a database which was created with an initial file size of 10 gig. Currently it is only using 2 gigs.
We have developers that want to have a copy of the database on their desktops, but do not have 10 gigs free space.
What is the best way to get them a copy of the database while reducing the footprint?
I have patch server on which the database is SQL server 2000.
The Database size is 27GB and I want to reduce the size of DB by deleting all the records and keeping only year 2007 records.
Please advise me how to reduce the size of the DB.
If I need to delete any record, please let me know how to delete it.
If their is any Query for it, please send the Query.
The DB contains total of 11 tables.
Thanks to All,
Mohammed Muzaffar.
My Database plan is as follows:
Full Database backup every night, Full recovery mode.
T-Log backups , every 4 hours.
both are appended to media,
there are no active transactions,
but Log file will not shrink or reduce in size.
what would you suggest.
Thanks,
I was working on a job to send me info each morning about database file free space and was noticing some odd things when looking at the log file VLFs for one of my databases in an AlwaysOn availability group.When I run DBCC LOGINFO on the secondary replica for this database, I get what I expect and most VLFs have a status of 0 (indicating the VLFs are reusable or unused). When I run DBCC LOGINFO on the primary replica, all of the VLFs have a status of 2 (active or recoverable).
Since log backups on the secondary replica in AlwaysOn still truncate the log in the primary replica, I would expect that the VLFs in the primary replica would also be mostly in a reusable or unused state. My log file sizes are the same size on each server and my backups are completing successfully. what might be causing the VLFs on the primary replica to have a status of 2 in DBCC LOGINFO when taking log backups from the secondary replica?
I have just finished configuring my first test mirrored environment (High safety mode). I setup the database engine service accounts on each of the servers with domainuser. I inherited a production mirrored environment set up by someone else. On the production servers the database engine service account is NT Authorityuser a local account. I am trying to practice installing Windows updates within a mirrored environment and I not sure how to proceed when the service account is NT Authority user account. should I change the service account to a domainuser?
View 2 Replies View Related
I have MS-Access as data source for one of the reports. I can preview the report fine from BI studio however, it does not work when I deploy it on report server.
The error is :
An error has occurred during report processing.
Cannot create a connection to data source '<data source name>'.
The Microsoft Jet database engine cannot open the file '<UNC location of the MS-Access database>'. It is already opened exclusively by another user, or you need permission to view its data.
MS-Access database is located on a different server.
Any help to solve this? I understand it has something to do with permission both on server where reporting service is running as well as the server where MS-access database is located. Pls help.
Hi, all experts here,
Thank you very much for your kind attention.
I am trying to modify the files path (primary file, log file) of databases, but it looks like I am not able to mofidy their files path directly from the database property dialogue? Would please any experts here give me some ideas on what else can I try to figure it out? Thanks a lot in advance and I am looking forward to hearing from you shortly.
With best regards,
Yours sincerely,
Hi folks,
Whts up........??? M back, after a long gap.
I have come across with a major issue. And u know wht th issue is.........???
It is about th DATABASE SEARCH TOOL. I have a database of around 30 tables. Now I wud like to have aa search engine on my .asp page. There will be a text box on th page and one submit button. After typing some text in th text area n submitting th page, my package sud check tht perticular text in all th COLUMNS of all th TABLES, n whrevr it gets a match (exactly same, or by speech recognition), it sud through th links on th next page.
Nw i wud like u guys to take this problem, at th earliest n come up with a up to th mark solution.
Thnkx
in Surface Area Configuration for Features.
What the problem is?
Thanks in advance.
i installed sql server 2005 but cant connect to database engine. i write (computer name)sqlexpress but it failed. error is:
TITLE: Connect to Server
------------------------------
Cannot connect to rezasqlexpress.
------------------------------
ADDITIONAL INFORMATION:
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)
in Surface Area Configuration for Features.
What the problem is?
Thanks in advance.
Hi,
I've just installed a brand new instance of SQL on a new server. It's for our Helpdesk/Inventory software. All I needed to install was the DB Components and the Tools (primarily for Management Studio). I did not install SQL as a default instance, but called it TRACKIT8 (software using this instance). I have subsequently installs SP2 before trying to connect to the DB.
I am unable to conect to the DB Engine via Management Studio. In the Server name: field, there is a blank space, so I try to browse for a server, but I get nothing. I try to manually type the name of the server (NIASSQL2, localhost etc) into the field, but I cannot connect to the engine at all. I can see the services for the Server and Server Agent have started in the SQL Server Configuration Manager and I am able to change certain field in the properties of the Server and Agent (change the path of the error logs).
Is there something I am supposed to configure before I try to connect via the Management Studio? I am looking to change the paths of the Data Files, as I don't want them on my C: partition. Plus I have other instances I want to create and install. Any help would be greatly appreciated.
Cheers.
SP2 failed for database engine.
Following is the error:
EventType : sqlsesetup P1 : do_sqlgroupmember P2 : 0x7348
P3 : do_sqlgroupmember P4 : 0x7348 P5 : sqlca_sqlsecurityca.cpp@1132
P6 : unknown P7 : sql9 P8 : hotfix@ P9 : x86 P10 : 3042
Product : Database Services (MSSQLSERVER)
Product Version (Previous): 1399
Product Version (Final) :
Status : Failure
Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixSQL9_Hotfix_KB921896_sqlrun_sql.msp.log
Error Number : 29512
Error Description : MSP Error: 29512 SQL Server Setup was unable add user NT AUTHORITYSYSTEM to local group DCCIBERSQLServer2005SQLAgentUser$DCSQL01$MSSQLSERVER.
any help appreciated...
I have recently installed SQL server 2005 express edition along with Sql server management studio expressI have run the installation twice and during the installation I have created two instances of SQL server one named instance having Windows authentication and the other default instance having mixed mode authentication. Now I want to delete the named instance. Could anybody tellme I can delete the named instance?
View 3 Replies View RelatedI can connect to Analysis Service and Integration Service but I can not connect to the Database Engine. I receive the following error message:
Cannot connect to homebase.
Additional Information:
An error has occurred whlie establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server doesn not allow remote connections. (provider: SQL Network Interfaces, error: 25 - COnnection string is not valid)(Microsoft SQL Server, Error:87)
(Click on OK to continue)
I'm a newbie to SQL and I inherited a 2008 R2 Sql server with about 20 different databases on it. Â I've noticed that more than half of the databases are encrypted and I was looking to encrypt the rest but for some reason I am unable to do so. Â I'm assuming I already have a master key created but when I go to Options of the DB and change Encryption Enabled from False to True I get and error, "Cannot change database encryption state because no database encryption key is set." Â How I could use my existing key to encrypt the databases or would I have to create a separate key for each db?
View 6 Replies View RelatedIf I am upgrading the SQL Server 2005 32 bit to 64 bit (only DB Server), Do Applications that communicate with Database need to be upgraded to 64 bit as well???
Thanks,
hello everybody!!!
I am very new to SQL SERVER 2005 EXPRESS EDITION
while connecting to DATABASE ENGINE i got this error message.....
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (.Net SqlClient Data Provider)
any solution please??????????
Hi! I have a little problem and I need your help.
I have recently installed SQL Server 2005 Developer Edition. I did not install all the features, just the Database engine and Analysis Services. I also updated everything with SQL Server SP2.
So far, so good. But when I tried to connect to the Database Engine, I get a little window with the following message:
"Cannot connect to Valentin-PC (server name)."
"Login failed for user - User Name - ( Microsoft SQL Server, Error: 18456 )".
I have tried just about everything, but to no avail. What is strange is the fact that I can connect to the other server type that I have installed (Analysis Services). No connection problems there.
Can anyone help?
Mucho gracias!
I just installed SQL server Enterprise 2005 and connected database engine in SQL server management studio, but it is blank space when choose 'browse for more' at server name field. What's reason to cause this problem?
Questions for this problem:
1. Do I need to open the TCP port 1433 in firewall by hand for connect database engine?
2. I can€™t find the sqlbrowser.exe in SQL server/90/ folder, can I copy this file into it.
3. The SQL server mobile is work; I can connect the new created database. What€™s different to use SQL server mobile and database engine?
4. I got five discs from Microsoft; I have installed DVD server application and pack 2. Do I need to install the library?
Thanks for any help.
Hi:
I'm an asp.net programmer and my database is in access format.My server is Windows Server 2003 Enterprise Edition Service Pack 1.I do not need microsoft access installed on my server to access my database through asp.net.
I want migrate my database to sql server 2005
Could I access my database in sql server 2005 format (mdf) through asp.net without sql server 2005 installed on the server?
could I buy to microsoft only "Database Engine"? orIs there a free distribution of "Database Engine"?
Thanks!!
Hi experts! I would like to ask for some help regarding Database Engine Tuning Advisor. I was trying to create Session Monitor then I choose TABLE as a workload. Then after creating and selecting the corresponding setup then I start the analysis, during the analysis it prompted an error?
Error MSG:
The specified workload(file or table) has no tunable events. Events must be one of the following types - SQL:BatchStarting, SQL:BatchCompleted, RPC:Starting, RPC:Completed, SP:StmtStarting or SP:StmtCompleted for workload trace file or table.
But if I tried to use the Workload FILE instead of the table the session is successful and completed the analysis. My SQL current setup is client only, I was just accessing the server. Pls help me how to fix or do I need to configure something? Badly needed your help experts. Thanks in advance.
Tatas move
Hi,
I am reading the book by Steven Roman:
"Access Database : Design and Programming" 3rd edition.
On page 120, Figure 7-2, he showed the the structure of the Jet Database Engine, which is very confusing to me.
According to this picture, I come to such an understanding:
1) VBA is just the hosting language for the Jet Database Engine;
2) Microsoft Visual Basic, Excel, Access, Word are hosting languages for VBA.
Isn't this weird? VB hosts VBA?
Thanks for any input!
Hi,
I'm just testing the database engine tuning advisor.
I created a basic trace file in SQL Server profiler by randomly opening a number of views in the Adventureworks db. I then used the Database Engine Tuning advisor to analyse this.
The tuning advisors recommendation was to drop about 2-3 dozen indexes, which sounds like a really dumb idea.
Is it normal for DET to recommend that any index not referenced by the workload file be dropped?
Hello all,I want to use the SQL Server 2005 Tuning Advisor on our database, andI'm hoping someone here can just confirm the steps for me. We have a10GB database that has a number of applications hitting it constantly,all performing many SELECTs, INSERTs and UPDATEs. Are the followingthe steps I need to take?1. Stop all the applications hitting the database.2. Make a backup of the database.3. Start the profiler on the original database.4. Turn on all the applications again.5. Wait for a time before turning off the profiler and saving theprofile.6. Run the tuning wizard against the backup database using the savedprofile.Do I have to do this every time or am I missing something obvious? I'munder the impression I need the backup because our database has a highvolume of INSERTs and UPDATEs so I have to make sure the state of thedatabase matches the state of the profile.As a matter of interest, why is it not possible for the tuning wizardto analyse the database using the *current* activity? Why does it haveto be a 'pre-recorded' workload?Many thanks,Tommy.
View 4 Replies View RelatedOur development organization has created an application employing FILESTREAM, which through the pilot has been incorporated into a schema within our Data Warehouse Staging database. Going to production, the development and BI teams have determined that they want it separated out into a separate database, and they'd like to separate it in the current pilot environment (DEV). How can I best move (or at least copy) the existing FILESTREAM data from the current database into a new one?
View 6 Replies View RelatedI have dataware house database and it's size is 2 TB with simple recovery model.I want to reduce it's size because everyday before loading table gets truncate.Is it best practice to shrink the datafiles?database having 5 data files and one log file.what is the best way to reduce?
View 7 Replies View RelatedI am looking to create a server wide trigger which will fire whenever a database gets created, I would like to know if there are any gotchas or anything that I need to look out for/test specifically.
The idea is to automatically grant some permissions and take some other actions whenever a database gets created.
I got the sql2005 std edition at the msdn rollout, and the vs2005 studio. I uninstalled sql2000 and now, cannot connect to the database engine.
I get the following error message with a link to an page that offers no info. What's up and how can I get it to work? Please, help.
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=2&LinkId=20476