We have "unrestricted" size of log file on model database, but for some reason, all newly created databases have "restricted" sizing.
After taking a look at this, it appears that SQL Server changes the "Unrestricted" file growth setting specified on the log file in the model database to "restricted to 2 TB". This size is the maximum allowed for log files by SQL Server 2005, so this means the settings are identical?
I have the following T-SQL which simply creates new database. However, in properties of this DB I see that LOG's max file size is restricted to 2 GB. Where's error?
CREATE DATABASE Bonus
ON PRIMARY
(
NAME = N'Bonus',
FILENAME = N'E:Bonus.mdf',
SIZE = 3072KB ,
[Code] ....
Env.: SQL2K5 on Win2003
I checked the templog to grow unrestricted but I see it is set to 2,097,152 MB.
The problem occured when the tempdb log file reached to 2GB and I got the following error:
Msg 9002, Level 17, State 4, Line 109
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
The value in log_reuse_wait_desc column in sys.databases is ACTIVE_TRANSACTION.
I tried to shrink the log file but it didn't work. To resolve the problem temporarily, I added another log file.
1. Is the 2,097,152 MB the maximum amount for the log size for a database?
2. Why I couldn’t shrink the log file?
3. How can I resolve the issue without adding another log file? Does a checkpoint solve the problem?
CanadaDBA
Has anyone had this problem or know how to prevent it? I received an error message yesterday from my SQL server (v7.0 with SP2) saying that the logfile for the tempdb was full. The logfile for tempdb is set by default to unlimited growth and the drive it is on has 24GB free. What causes this error to happen?
View 3 Replies View RelatedI am trying to set the log file on one of our databases to unrestricted growth, but after changing the setting, saving and then going back into management studio, the setting is now back to restricted growth. I am a sysadmin within sql and a local admin on the server so permissions should not be an issue.
What is preventing the setting of unrestricted growth on the log file from staying after the save.
I do not have this problem on the data file, (the change stays), but only on the log file. it happens for any database on the server. Sql Server 2k5 enterprise edition with SP2.
thanks
I am download backup of SQL database from server and I am restore database successful into my SQL Server Management Studio Express, but near name of database stay: (Restricted User) note!When I try use database offline for login into my control panel created by me it not work. After I create small new database with same tables login work! I am admin on my local PC. Any ideas why my restored database has note: "Restricted User"? This is bigger database and looks complicate transfer data from old to new database, but if someone have these examples it can help too
View 2 Replies View RelatedHi all,
What is the syntax to set database to restricted user mode using SQL query analyzer?
Hi,
I have a problem.While creating login for a single table in a database we know the public role is given by default.
I have use to disabled all public priviledges by-
Use northwind
revoke all from public
executing through query Analyser.
And after creating a role named "reception" and giving the priviledges of "select' to customer table by ticking and assigning that role to the login I get my login.
But using that login When I login,I can use select to see customer table.
And except that nothing is allowed.
But when i write
use pubs
select * from sales
I get the entire results.
Plz suggest me a way to block everything except the "select" of customer table.
Thanks!!
Joydeep
Hello Everyone,
I appear to be doing something wrong. When I
set my Table for Unrestricted File Growth and
save it, When I go back and look at the settings
it is set back to restricted file growth.
It seems to me if you set any settings and apply
and/or save it. Should it not keep that setting.
Any advice would be great.
Thanks
Scenario:
SQL Server 2005 SP2.
SQL Server has only Windows Authentication Mode.
SQL server 'xp_cmdshell' option has been disabled by surface area configuration.
A database 'CN01' has been created
I have created a schema 'Executables' that contains stored procedures to access data in 'CN01'.
I have create a database role 'db_procexecutor' with execution permission on schema 'Executables'.
I have created a group 'CN01-Users' in Active Directory.
I have created a login 'CN01-Users' wich has been asigned 'db_procexecutor' role in 'CN01' database.
Problem:
I want a user member of AD group 'CN01-Users' to execute a Stored Procedure in 'CN01' database, (in 'Executables' schema) wich needs access to 'xp_cmdshell' to write a file in OS through bcp command.
The following SP works only assigning 'sysadmin' role to 'CN01-Users'.
For security reasons, I don't want to assign 'sysadmin' role to 'CN01-Users', neither assign permissions on Master.
I want the SP to work in the security scenario described earlier.
Should I use procedure signing ? ... If so, please describe step by step.
Sample SP:
EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE
exec xp_cmdshell 'bcp "SELECT Column FROM CN01.dbo.table" queryout C: able.txt -T -c'
EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
Thank you !
Daniel Pérez
Montevideo-Uruguay
Just released small piece of software with SQL Compact Edition, version 3, dll files distributed with the application.
Now feedback from 2 people suffering an error message as follows:
SQL Mobile usage is restricted on this platform. To use SQL Mobile, you must install SQL Server 2005, Visual Studio 2005, or the Tablet PC SKU.
Both using XP under Admin accounts.
My own testing did throw up this error prior to release, but only when I tried to run the program as a Limited User. I was unable to find out why. Running the program under an Administrator account never produced this error message.
The test-bed was a fresh install of XP, SP 2, running within VMware. An Administrator account was set up. No development software was installed, nor anything to do with SQL. I tried to make it a clean environment to be used by a non-technical user.
Any insights would be most appreciated.
I am trying to resize a database initial log file from 500M to 2M. I€™m using€?
ALTER DATABASE <DBNAME> MODIFY FILE ( NAME = <DBLOGFILENAME, SIZE = 2 ) "
And I'm getting "MODIFY FILE failed. Specified size is less than current size." I tried going into the database properties and setting the log file to 2M, but it doesn€™t keep the changes.
Any help with this process?
Access 97 SR 2 Front EndSQL Server 7.00.1063 Back EndWhen I attempt to relink the tables in my Access app. I am suddenlygetting this weird error message = "ODBC--Call Failed", followed by"[Microsoft][ODBC SQL Server Driver]Restricted Data Type AttributeViolation(#0)". This problem did not manifest last time I tried a fewweeks ago, and so far as I know there have been no software changessuch as Service Packs etc.The error occurs when attempting to link a view. Here is the scriptfor the tables and the view siipl_DoesNotWork:==================================CREATE TABLE [dbo].[tblBOMEntries] ([fldID] [int] IDENTITY (1, 1) NOT NULL ,[fldCMMPartID] [int] NULL ,[fldSIPartNo] [varchar] (20) NULL ,[fldDescription] [varchar] (50) NULL ,[fldQtyAssy] [int] NULL ,[fldItemNo] [int] NULL ,[fldOnIPL] [bit] NOT NULL ,[fldRefDesig] [text] NULL ,[fldParentID] [int] NULL ,[fldBOMLevel] [int] NULL ,[sys_ts] [timestamp] NULL ,[fldObsDate] [varchar] (15) NULL ,[fldEffDate] [varchar] (15) NULL ,[fldItemRevNo] [int] NULL ,[fldModNo] [varchar] (10) NULL ,[fldNewBOMFlag] [smallint] NOT NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOCREATE TABLE [dbo].[tblCMMIndex] ([fldCMMPartID] [int] IDENTITY (1, 1) NOT NULL ,[fldMainDescription] [varchar] (50) NULL ,[fldSIPartNo] [varchar] (50) NULL ,[fldBOMRevision] [varchar] (5) NULL ,[fldBOMAttached] [bit] NOT NULL ,[fldBOMDate] [datetime] NULL ,[fldCMMID] [int] NULL ,[sys_ts] [timestamp] NULL ,[fldLink] [int] NULL ,[fldNewBOMRevision] [varchar] (5) NULL ,[fldNewBOMAttached] [bit] NOT NULL ,[fldNewBOMDate] [datetime] NULL ,[fldSiteID] [int] NULL) ON [PRIMARY]GOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGOsetuser N'siipl'GOCREATE VIEW siipl.DoesNotWorkASSELECT dbo.tblBOMEntries.*,dbo.tblCMMIndex.fldCMMPartID AS Expr1FROM dbo.tblCMMIndex INNER JOINdbo.tblBOMEntries ONdbo.tblCMMIndex.fldCMMPartID = dbo.tblBOMEntries.fldCMMPartID================================However, if I create a new view based on exactly the same SQL asDoesNotWork I don't get the same error message:CREATE VIEW dbo.DoesWorkASSELECT dbo.tblBOMEntries.*, dbo.tblCMMIndex.fldCMMPartID AS Expr1FROM dbo.tblCMMIndex INNER JOINdbo.tblBOMEntries ONdbo.tblCMMIndex.fldCMMPartID = dbo.tblBOMEntries.fldCMMPartIDThe only difference that I can deduce between siipl.DoesNotWork anddbo.DoesWork is the ownership. Can that be the problem? If so, why?TIAEdward
View 1 Replies View RelatedHow would I hide the sa account to be accessible under Logins for a restricted User in sql server 2005 and also other folders like Replication, Managment, etc need to be hidden too.
any idea,
thank,s
I restored a sql server database today. There is a problem though. In SQL server, right next to the name of the database are the words "Restricted User". Their seem to be some limitations on my access to this database now as I am not able to run some of my C# code against the database. How can I fix this in SQL server so that I am not a Restriced user.
Example of what I am talking about. In SQL server my database is named Gorlaz. Just to the left of this is a yellow Icon that represents the database "Gorlaz". For illustration purposes I will use the character "$" to substitute for the yellow database icon. So this is how it reads in SQL server management studio.
$Gorlaz(Restricted User)
Secondary replica database(setup in async mode) of AlwaysON went in "restricted mode" during weekly reindex operation.
So I have tried below steps
1) Executed following statement on the same secondary replica database where the issue exists
alter database <DBNAME> set multi_user with rollback immediate
but it failed with the error saying "The operation cannot be performed on database "dbname" because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group. ALTER DATABASE statement failed."
2) Primary database is multi_user but still tried following command on primary replia database(thinking it will replicate)
alter database <DBNAME> set multi_user
but no luck. The secondary alwaysON database shows (synchronizing) as the alwaysON is set in async mode but the command doesn't replicate across secondary
so we are left with the only option to re-setup alwaysON but I want to avoid it as database size is huge..
any other options or am I missing anything?
MCP,MCDBA(SQL 2000),MCTS(SQL 2005),MCITP(SQL 2005)
Hello--
We're running into an issue where analysts are having problems obtaining lift charts (via the Mining Accuracy Chart UI available in the Visual Studio Analysis Services project) and performing prediction (via the Mining Model Prediction UI).
The issue seems to be related to the underlying analyst security model. Note that this post is related to:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=924641&SiteID=1
Analysts that work on the same problem will only have access to:
- A sandbox relational database (which contains views into the same source database). The analyst is db_owner of the sandbox database, so she/he can create data transformations required, etc. The sandbox database contains views to the source database, but the analyst only has read-access to the specific data elements needed from the source DB. So, they are very restricted w.r.t. the source database, but are db_owners of their sandbox relational databases. Note that the analyst will connect to he database via Windows Authentication.
- An Analysis Services sandbox database to use for their modeling, etc. In this AS sandbox db, we've created a role called "Administrator" and checked the permissions: Full control (Administrator), Process database, and Read definition. The analyst's windows account is the "user" associated with this role.
Also, in this situation, the SQL Server 2005 Relational Engine and Analysis Services are running on a single machine. The goal of this security model is to provide analysts with the ability to work in their "workspaces" (both SQL and AS), but not to see other analysts work, etc.
Under this model, Analysts are able to deploy mining models when the Data Source object that points to their relational "sandbox" DB is set-up with "Impersonation Information" = "Use a specific user name and password", where the Analyst provides their domain account information.
But, when trying to build a lift chart using the same data source view objects that were used to successfully train the model, the following error is occurring consistently:
Window Title: "Loading Mining Accuracy Chart"
Window Text: "Failed to execute the query due to the following error: Execution of the managed stored rocedure GenerateLiftTableUsingDatasource failed with the following error: Exception has been thrown by the target of invocation. Either '<domain><login>' user does not have permission to access the '' object, or the object does not exist. Errors in the high-level relational engine. A connection could not be made to the data source specified in the query. Errors in the high-level relational engine. A connection could not be made to the data source specified in the query.."
Since the Analyst was able to build the model with her/his given '<domain><login>' credentials, it is puzzling why the lift chart is failing.
Thanks in advance for your help,
- Paul
Hello again,and thanx again -I now found something unexpected - that hopefully is the cause of myproblem? :In Man. studio security anmeldungen (engl. login names or users oraccounts or so?) right clicking on any user and properties, status
Quote:
I have two xml source and i need only left restricted data.
how can i perform left restricted join?
Hello.
View 5 Replies View RelatedGreetings,VWD EE and other Tools do not have problems working O.K. on my machine, when I am logged-on as restricted(limited) user, both environment and local web server are functioning, and it was MS effort to do it right.BUT the Problem is, when I try to connect to database->new DB connection(either through vwd or management studio etc). I get an Error - which should be written to event log (according to msg) but it isn't - that I can't connect. I am sure that it is because NO SQL SERVER2005EXPRESS instance is running, which I think is the only prerequisite to have it work (I do not need sqlbrowser service I am doint local development).SQL2005express service is configured on MANUAL start. I can start it as administrator through SQL config manager, but it is not convienient and what I want. I need to start it ONLY as a developer user, I do not want it to be running all-the-time for everyuser using computer. It was congigured as Network Service logon, I tried Local Service logon, and I even tried configuring it to logon as -my developer user- account, e.g. with limited user name and his password.In every case I can't start the service as member of users group and this developer. Then I added limited user to SQL2005EXPRESS group. Still NO help, won't start either.My question is, If I want to start developing ADO2.NET application and I need to have running SQL2005Express instance as a develper, how can I start it? I think I do not have some rights to masterDB or something. Do it allways need to be running when computer starts? Isn't there any other way, to start it JUST when I think I start developing?I know of user-mode of accessing SQL2005express DBs, but it also assumes that SQL2005Express service is already running, what I am trying to prevent. I do not want to have it running for everyone who uses computer, just for someone who neeeds it.Any help explaining me the right way HOW TO SETUP WORKING ENVIRONMENT when developing as limited user welcome, I read the the documentations and haven't found the answers.THANK YOU !
View 1 Replies View RelatedI installed sql 2005 a while back. Then I recently found out my file system was fat32 (I don't understand why the hardware people did this...) and I had to convert to NTFS. Naturally the sql service no longer worked so I uninstalled inorder to reinstall now I can't reinstall it I keep getting this message
native_error=5039, msg=[Microsoft][SQL Native Client][SQL Server]MODIFY FILE failed. Specified size is less than current size.
I'll try to post the full log in a new post.
Hi folks,Can anyone enlighten me here? I'm trying to use a SPROC which, when supplied with an int, looks up the table and returns certain columns from it. I'm using a SqlCommand, here's my codebehind: ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SqlCommand dataSource = new SqlCommand("retrieveData", new SqlConnection(dbConnString)); dataSource .CommandType = CommandType.StoredProcedure; dataSource .Parameters.AddWithValue("id", poid); dataSource .Parameters.AddWithValue("title", title).Direction = ParameterDirection.Output; dataSource .Parameters.AddWithValue("creator", creator).Direction = ParameterDirection.Output; dataSource .Parameters.AddWithValue("assignee", assignee).Direction = ParameterDirection.Output; etc, etc... And the SPROC:------------------------------------------------------------------------------------------------------------------set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[retrieveData] @id int, @title varchar(50) OUTPUT, @creator varchar(50) OUTPUT, @assignee varchar(50) OUTPUT, @contact varchar(50) OUTPUT, @deliveryCost numeric(18,2) OUTPUT, @totalCost numeric(18,2) OUTPUT, @status tinyint OUTPUT, @project smallint OUTPUT, @supplier smallint OUTPUT, @creationDateTime datetime OUTPUT, @amendedDateTime datetime OUTPUT, @locked bit OUTPUT AS /**SET NOCOUNT ON; **/ SELECT [title] AS [@title], [datetime] AS [@creationDateTime], [creator] AS [@creator], [assignee] as [@assignee], [supplier] as [@supplier], [contact] AS [@contact], [delivery_cost] AS [@deliveryCost], [total_cost] AS [@totalCost], [amended_timestamp] AS [@amendedDateTime], [locked] AS [@locked] FROM purchase_orders WHERE [id] = @id; ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ The id being passed in is definately not null, and is set to a value of an item I know exists. The resulting error is:
Exception Details: System.InvalidOperationException: String[1]: the Size property has an invalid size of 0.Line 63: retrievePODetails.Connection.Open();Line 64: retrievePODetails.ExecuteNonQuery();[InvalidOperationException: String[1]: the Size property has an invalid size of 0.] System.Data.SqlClient.SqlParameter.Validate(Int32 index) +717091... ... Can anyone see anything I'm missing? Thanks,Ally
Using C#, SQL Server 2005, ASP.NET 2, in a web app, I've tried removing the size from parameters of type NCHAR, NVARCHAR, and VARCHAR. I'd rather just send a string and let the size of the parameter in the SP truncate any extra chars if need be. I began getting the error below, and eventually realized it happened only with output parameters, as in the code snippet below.String[3]: the Size property has an invalid size of 0. par = new SqlParameter("@BusinessEntity", SqlDbType.NVarChar); par.Direction = ParameterDirection.Output; cmd.Parameters.Add(par); cmd.ExecuteNonQuery();What's the logic behind this? Is there any way around it other than either finding out what the size should be, or assigning a size larger than would ever be needed? ThanksMike Thomas
View 6 Replies View RelatedI have one db test with one .mdf and .ldf file...mdf file size is 100mb and for some reson i removed all the tablesfrom that .mdf file and transfer it into new secondary file so all thetables moved into secondary file now i want to reduce the first .mdffile from 100 mb to 50mb is that possible,it's showing 90mb is free.Please reply
View 1 Replies View RelatedI want to know encrypted data's size for designing database field size.
For example, cardnumber varchar(20) Encrypted by Triple_DES and PassPhrase, How match size does need to encrypted data store field.
I think the size does not depend to PassPhrase char length.
Regards,
Yoshihiro Kawabata
I am getting error to run stored procedure using executenonquery method. The Stored Procedure is having OUTPUT parameter.
ExecuteNonQuery statement is called using SqlHelper.
Error : String[18]: the Size property has an invalid size of 0
Just wanted to know what is a general rule of thumb when determining log file space against a database's data file.We allow our data file for our database to grow 10%, unlimited. We do not allow our log file to autogrow due to a specific and poorly written process (which we are in a three month process of remove) that can balloon the log file size.Should it be 10% of the Data file, i.e. if the Date file size is 800MB the log file should be 8MB?I realize there are a myraid of factors that go against file size but a general starting point would be nice.ThanksJeff--Message posted via http://www.sqlmonster.com
View 4 Replies View RelatedThe Tabular model is showing 19 GB on disk, but it is acquiring around 40 GB in memory.
View 3 Replies View RelatedHi,
An MSSQL DB running SAP indicates a smaller DB size (MMC & SAP) than the actual physical size. The difference is about 8 GB.
A lot of records were deleted before this. Did they remain in the DB as NULL values or something ?
Does anyone know what the reason for this could be ? And how to clean this up ?
Thanks in advance,
Paul
Hi,
i use this script that show me the size of each table and do the sum of all the table size.
SELECT
X.[name],
REPLACE(CONVERT(varchar, CONVERT(money, X.[rows]), 1), '.00', '') AS [rows],
REPLACE(CONVERT(varchar, CONVERT(money, X.[reserved]), 1), '.00', '') AS [reserved],
REPLACE(CONVERT(varchar, CONVERT(money, X.[data]), 1), '.00', '') AS [data],
REPLACE(CONVERT(varchar, CONVERT(money, X.[index_size]), 1), '.00', '') AS [index_size],
REPLACE(CONVERT(varchar, CONVERT(money, X.[unused]), 1), '.00', '') AS [unused]
FROM
(SELECT
CAST(object_name(id) AS varchar(50)) AS [name],
SUM(CASE WHEN indid < 2 THEN CONVERT(bigint, [rows]) END) AS [rows],
SUM(CONVERT(bigint, reserved)) * 8 AS reserved,
SUM(CONVERT(bigint, dpages)) * 8 AS data,
SUM(CONVERT(bigint, used) - CONVERT(bigint, dpages)) * 8 AS index_size,
SUM(CONVERT(bigint, reserved) - CONVERT(bigint, used)) * 8 AS unused
FROM sysindexes WITH (NOLOCK)
WHERE sysindexes.indid IN (0, 1, 255)
AND sysindexes.id > 100
AND object_name(sysindexes.id) <> 'dtproperties'
GROUP BY sysindexes.id WITH ROLLUP) AS X
ORDER BY X.[name]
the problem is that the sum of all tables is not the same size when i make a full database backup.
example of this is when i run this query against my database i see a sum of 111,899 KB that they are 111MB,but when
i do full backup to that database the size of this full backup is 1.5GB,why is that and where this size come from?
THX
Here are my scenarios:
We have an application with replicated environment setup on sql server 2012 . Users will have a replica on their machines and they will replicate to the master database. It has 3 subscriptions subscribed to the publications on the master db.
1) We set up a replica(which uses sql server 2012) on a machine with no sql server on it. After the initial synchronization(used replmerge tool) the mdf file has grown to 33gigs and ldf has grown to 41 gigs. I went to sql server management studion . Right click and checked the properties of the local database. over all size is around 84 gb with little empty free space available.
2) We set up a replica(which uses sql server 2012) on a machine with sql server 2008 on it. After the initial synchronization(used replmerge tool) the mdf file has grown to 49 gigs and ldf has grown to 41 gigs. I went to sql server management studio , Right click and checked the properties of the local database. over all size is around 90 gb with 16 gb free space available.
3) We set up a replica(which uses sql server 2012) on a machine with sql server 2012 on it. We have dropped the local database and recreated the local db and did the initial synchronization using replmerge tool. The mdf file has grown to 49 gigs and ldf has grown to 41 gigs. I went to sql server management studio , Right click and checked the properties of the local database. over all size is around 90 gb with 16 gb free space available.
Why it is allocating the space differently? This is effecting our initial replica set up times.
It is possible to find table size and in that table each row size.
View 4 Replies View Related